SQL Training - 06

create Partition Function fnDOB(DateTime)

AS Range LEFT 

for values ('20091231','20101231','20111231','20121231')

create partition scheme schDOB as partition fnDOB

to(D_LT2010, D_2010, D_2011, D_2012, D_GT2012)

If you use partitioning in your DB, we recommend to use field not change periodically 

Fetch

select * from Person

order by Id

OFFSET 10 ROWS

FETCH NEXT 10 ROWS ONLY

Row Number

SELECT top 10 *

FROM    (

        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn

        FROM    Person

        ) p

WHERE   rn > 0

ORDER BY  id

Create table from select

Select * into new_table  from  old_table 

Logging Delete records in another table

delete from Person_Old 

output deleted.id, deleted.FName, deleted.LName

Into deleted_person(ID, fName, LName)

where ID=350

DateDiff the date different 
select  DATEDIFF(day,'19870516',getdate())/365.24 as age


End of month 
select  EOMONTH(getdate())

Check if date or not(1,0)

select  isdate(getdate())


Concatenate and Convert

select top 10 'Name: '+ fname +'  '+lname +', DOB= '+convert(nvarchar, dob) + '  Gender: '+
case
when Gender=1
then 'Male'
when Gender=0
then 'Female'
end
as userDesc
from Person