SQL SERVER TUTORIAL IN DEPTH

The blog is to help users about sql server

Others

LightBlog

Breaking

Monday, 30 September 2013

September 30, 2013

LOCKING HOW IT HAPPENS IN REAL TIME.[SP_WHO2]


Split the vertical tab.First the query window 52 followed by 53.

Execute in query window 52.

begin transaction
select
update

execute in query window 53.
select






Now execute which are ticked.....






53 is blocked by 52. And the wait time increases ...never stops unless and untill you commit the transaction in 52.

Once you commit the transaction in 52. Lock gets released.  

SP_WHO2 

The above command tells you the details about the blocking











0
By at No comments:
September 30, 2013

ISNULL ,COALESCE AND NULLIF TO TACKLE NULL VALUES




ISNULL(ARGUMENT1,ARGUMENT2)

 COALESCE(ARG1,ARG2,[ARG3,ARG4,])

--isnull /*They are like inline if statement

 if argument1 is null

 return argument2

 else

 return argument1;

 end if--only for isnull

 */
--Colaesce[allows you to extend after argument2....]
/*
if argument1 is null
return argument2
else if argument2 is null
return argument3
*/

isnull(middlename,' ') --if middle name is null replace by ' ' 

=====================COALESCE===============================

select * from dbo.main












select address_id,coalesce(distance,hub_id,0) from main














===================NULLIF========================================


--if argument1 = argument2 then return null
--else return argument1
select nullif(0,0)



select nullif(0,4)









select * from dbo.main


















select address_id,hub_id,location,
address_id/hub_id from main











select address_id,hub_id,location,
address_id/ nullif(hub_id,0) from main



















select address_id,
hub_id,
location,
isnull(address_id/ nullif(hub_id,0),address_id) as ratio1,
address_id/ISNULL(nullif(hub_id,0),address_id) as ratio2
 from main



0
By at No comments:

Friday, 20 September 2013

September 20, 2013

HOW TO FIND Nth HIGHEST NUMBER THE COMMON INTERVIEW QUESTION. [OFFSET/FETCH USE TO FIND THE SAME]


>>>>>>HOW TO FIND Nth HIGHEST NUMBER<<<<<



--create a table
create table #temp1
(
id int
)
--insert all the records
insert into #temp1(id)
select 100
union all select 200
union all select 300
union all select 400
union all select 500
union all select 600

select * from #temp1



















==Now lets see how to find any row number===================

select top 1 * from #temp1 where id not in
(select top 1 * from #temp1)














--inner query gives 100. select top from rest (200,300,400,500). so 200--Now this gives me the 2nd row pretty cool...

--NOw let me try to get the 3rd row of the table... select top 1 * from #temp1 with(nolock) where id not in(select top 2 * from #temp1)












--o/p 300 superb
--so we got a technique to find out any row number from a table which we want.


--But wait our goal was to find second highest number in the table

 <<<<USE MAX TO GET 2nd highest number>>>


select max(id)  from #temp1 with(nolock) where id not in
(select max(id)  from #temp1)










--the inner query gives me 600. now get the maximum from the numbers leaving 600.So 500.
--Wow that is cool ..so we have replaced top with max

=======================================================================



We have found a simple answer... Now here you may be trapped by an interviewer ...If you are asked to find the 3rd highest... this is not GOING TO  work

===trick 1 to get third highest==========================

--Now lets try this 3rd highest is 400. we need to get 400 by hook or crook.

select min(id) from #temp1 where id in(
select top 3  id from #temp1 order by id desc)

inner query gives me













--so out of that which is minimum. Obviously 400. cooool :)
--Now we are using Min and finding that 400. 





Lets make some twist.we will not use Min.we will use only top.Have a look at the below query incredible :)

===============trick 2 to get third highest=============================

select top 1 * from #temp1  where id in(
select top 3  id from #temp1 order by id desc)
order by id asc

--explanation...












--inner query gives the above result.
--Then we are arranging by ascending order[400,500,600] and fetching the top value.,,,,quite simple....

==============trick 3 use of Row number And Rank number================


select * from
(select id, ROW_NUMBER() over (order by id desc) as rownum from #temp1
)r
where rownum=3

--the inner query gives us the below result. Then rownum=3 is 400.
















--o/p 4 400 this is really awesome.



--================correlated queries============================

select * from #temp1 main where
3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)



--explanation
--Main        Auxillary
--100         --100
--200         --200
--300         --300
--400         --400
--500         --500
--600         --600

--now pick 100 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 100 in main table 
---answer is 6

--now pick 200 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 200 in main table
 ---answer is 5

--now pick 300 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 300 in main table 
---answer is 4

--now pick 400 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 400 in main table
--- answer is 3====we need this one

--now pick 500 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 500 in main table 
---answer is 2

--now pick 600 from main table ... now check in auxillary table how many in auxillary table are greater than 100 in main table 
---answer is 1

-- This is pretty cool. Hope you understood that


--======Same thing but using self join=================================
select main.id from #temp1 Main
join #temp1 auxillary on main.id=auxillary.id
where 3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)


=======DISTINCT COMES INTO ROLE NOW==================================
--100
--100
--200
--300
--400
--400
--500
--600
--600

-- In these cases use distinct
--lets try this

insert into #temp1(id)
select 400
union all select 600

select * from #temp1

--100
--200
--300
--400
--500
--600
--400
--600

--now let me try the correlated query
select * from #temp1 main where
3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)


select main.id from #temp1 Main
join #temp1 auxillary on main.id=auxillary.id
where 3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)



--explanation--> picking the value from main table ...and checking in auxillary table that is   how many are greater than main table[3=3]
i.e if 500 is picked we see that ..in auxillary table.[500,600 and 600]. so answer is 500.

--oops 500 which is not the 3rd highest
--this is where your distinct comes into role


select distinct(id) from #temp1 main where
3=(select count(distinct(auxillary.id)) from #temp1 auxillary where auxillary.id>=main.id)


select distinct(main.id) from #temp1 Main
join #temp1 auxillary on main.id=auxillary.id
where 3=(select count(distinct(auxillary.id)) from #temp1 auxillary where auxillary.id>=main.id)


 =======================================================================

--Earlier we  used one technique you remember

select min(id) from #temp1 where id in(
select top 3  id from #temp1 order by id desc)

--Now as we have inserted duplicated this wont work.Use distinct with top like below >> remember top should be followed by distinct,,,many do wrong here like top 3 disitinct(id) which is wrong

select min(id) from #temp1 where id in(
select  distinct top 3 id   from #temp1 order by id desc)


========WONDERFUL CONCEPT NOW[FETCH OFFSET] IN SQL SERVER 2012========

THIRD HIGHEST
SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

--O/P 400

--arrange by descending order. leave the first two.fetch the next.That is 400.
========================================================================
--2ND HIGHEST
SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

This is pretty cool right
--offset 1 means skipping the 1st row and fetch next 1 rows is nothing but the second row which is 2nd highest

========================================================================


As we have learned so far this much why not continue and learn the offset fetch

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 2 ROWS
--o/p
--400
--300
--200
--100

--offset can be written without fetch but not the vice versa
--it means OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY --you have to write this way

 =======================================================================
Wait if we do like this here both offset and fetch are there but still error [SO WE CANT WRITE FETCH NEXT 0 ROWS].

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 0 ROWS FETCH NEXT 0 ROWS ONLY;

--error The number of rows provided for a FETCH clause must be greater then zero.




====[FETCH FIRST] OR [FETCH NEXT] BOTH ARE SAME ..BOTH ARE INTERCHANGEABLE=====

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 1 ROWS FETCH FIRST 5 ROWS ONLY
--O/P
--500
--400
--300
--200
--100

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 1 ROWS FETCH NEXT 5 ROWS ONLY

--O/P
--500
--400
--300
--200
--100


=============ROW AND ROWS BOTH ARE SAME ..BOTH ARE INTERCHANGEABLE=====

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 1 ROWS FETCH FIRST 5 ROWS ONLY
--O/P
--500
--400
--300
--200
--100

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 1 ROW FETCH NEXT 5 ROW ONLY

--O/P
--500
--400
--300
--200
--100

========================================================================
SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 0 ROWS FETCH NEXT 0 ROWS ONLY;

--error The number of rows provided for a FETCH clause must be greater then zero.
 --HOW TO AVOID THIS ERROR

1ST WAY
DECLARE @VAR INT
SET @VAR=0

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 0 ROWS FETCH NEXT @VAR ROWS ONLY;

--SELECT ALL AND PRESS F5 IT WORKS ,,,, COOL :)

2ND WAY

SELECT distinct(id)
FROM #temp1
ORDER BY id DESC
OFFSET 0 ROWS FETCH NEXT (SELECT 0) ROWS ONLY;

--THIS WORKS AGAIN,,,,SUPERB :)


--NOW THE OFFSET/FETCH IS SIMILAR TO TOP FUNCTION .... THEN WHAT IS THE DIFFERENCE ,,WHAT IS THE ADVANTAGE,,,,WHEN WE SHOULD GO FOR TOP ,,, WHEN WE SHOULD GO FOR OFFSET/FETCH ??

--OFFSET/FETCH ALLOWS SKIPPING WHERE AS TOP DOESNOT.

--ORDER BY is mandatory to use OFFSET and FETCH clause WHICH IS DISADVANTAGE OF OFFSET... WHERE AS ITS NOT MANADATORY FOR TOP

--The TOP filter is more flexible than OFFSET/FETCH in the sense that TOP is allowed in modification statements, whereas OFFSET/FETCH isn’t allowed

--FETCH doesn’t support WITH TIES or PERCENT You can approximate the percentage by using a subquery but you may not get the same number of records as using TOP n PERCENT
0
By at No comments:

 test