SQL Statements

--1) Reseed Indentity column
--drop table TABLE_NAME
if  exists (select * from sys.objects where name='TABLE_NAME')
drop table TABLE_NAME
create table TABLE_NAME (ID int primary key identity (1,1),Increment int )

insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
select 'Max of id is ' ,max(id) from TABLE_NAME
delete from TABLE_NAME

select 'Max of id after delete  ', isnull(max(id),0) from TABLE_NAME
insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
select 'Max of id after delete  insert', max(id) from TABLE_NAME


--Reseed the Identity column with with one, so that the autoincremented
delete  TABLE_NAME
DBCC CHECKIDENT (TABLE_NAME, RESEED, 1)

insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
insert into TABLE_NAME (Increment ) values(1)
select 'Max of id after Reseed', max(id) from TABLE_NAME

--Or use  Truncate table  to reseed
Truncate table TABLE_NAME
go

--2) Wait after an operation
insert into TABLE_NAME  (Increment) values(0)
select * from TABLE_NAME
go
update TABLE_NAME  set Increment=Increment +1
print 'will update after 5 seconds'
go

--Wait delay example. The follwoing code induces a wit on the execution sequence in sql server
WAITFOR DELAY '00:00:05'
select * from TABLE_NAME


--Partition by example
--Rank() Row_number example
--Google for more explanation
UPDATE STATISTICS TABLE_NAME

select
*,
Rank() over(

partition by Rating
order by Person,App
)  rank1
,
row_number() over(
partition by Rating
order by Person,App
)  rank2

from
(
select 'Sam' as Person,'Angry birds'as App, 5 as Rating
union all
select 'Jane' ,'Angry birds', 2
union all
select 'Sam' ,'Ninja', 4
union all
select 'Jane' ,'Ninja', 4
union all
select 'Sam' ,'Ninja Guru', 5
union all
select 'Jane' ,'Ninja Guru', 5
union all
select 'Sam' ,'MyMaps', 1
union all
select 'Jane' ,'MyMaps', 3
union all
select 'Sam' ,'GMaps', 3
union all
select 'Jane' ,'GMaps', 4
) Ratings



GO

--Return multiple sql server rows as single comma seperated string
select
'ApprovedFruits are' Approved,
substring(
(
select
','+Fruit from  
(
select 'Grapes' as Fruit
union all
select 'Mango'
union all
select 'Blue Berries'
union all
select 'Apple'
) T
for xml path('')
)
,2
,8000
 ) as FruitList

go