TSQL Functions in SQL 2012

SQL 2012 has introduced several windows functions and below SQL code gives an idea on how to they work. Some of the functions such as row_number(),rank(),dense_rank(),Ntile() and grouping _sets() work in older versions as well.

Create Table WindowFunctions(id int,Sname varchar(20),Scity varchar(20))
GO
Insert into WindowFunctions
Values(1,'Martin','Dallas'),
(2,'Martin','Dallas'),
(3,'Martin','Houston'),
(4,'Martin','Austin'),
(5,'Sheri','Dallas'),
(6,'Sheri','Dallas'),
(7,'Sheri','Houston'),
(8,'Sheri','Austin')

--Row Number():
Select *,Row_Number() Over (Partition by Sname Order by Scity) as [RowNumber] from WindowFunctions

--Rank():
Select *,Rank() Over (Partition by Sname Order by SCity) as [Rank] from WindowFunctions

--Dense Rank():
Select *,Dense_Rank() Over (Partition by Sname Order by Scity) as [DenseRank] from WindowFunctions

--Lag():
Select *,LAG(id) over (Partition by Sname Order by Scity) as [LAG_ID] from WindowFunctions

--Lead():
Select *,LEAD(id) over (Partition by Sname Order by Scity) as [LEAD_ID] from WindowFunctions

--First Value():
Select *,FIRST_VALUE(id) Over (Partition by Sname Order by Scity ROWS between 1 preceding and 2 following) as FirstValue from WindowFunctions

--Last Value():
Select *,LAST_VALUE(id) Over (Partition by Sname Order by Scity ROWS between current row and 2 following) as LastValue from WindowFunctions

--Sum():
Select *,SUM(id) OVER (Partition by Sname Order by Scity ROWS between current row and 2 following) as [Sum] from WindowFunctions

--Grouping Sets():
Select count(id) as [Count_of_ID],Sname,Scity from WindowFunctions GROUP BY Grouping Sets ((sname,scity),(Sname),(Scity))

--Max():
Select *,MAX(id) OVER (Partition by Sname Order by Scity ROWS between unbounded preceding and unbounded following) as [MaxValue] from WindowFunctions

--Min():
Select *,Min(id) OVER (Partition by Sname Order by Scity ROWS between unbounded preceding and unbounded following) as [MinValue] from WindowFunctions

--NTILE():distributes the result set over the NTILE range
Select *,NTILE(3) OVER (Order by Scity) as [DistrutionRange] from WindowFunctions

Advertisement

Querying XML data in SQL – XML Explicit

Below code shows 1.retrieving the data using XML explicit and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example.
--Generates XML data and inserts the data into XML column.
declare @a table(sno int,sname xml)
Insert into @a
Select 1,(Select 1 as Tag,Null as Parent,FirstName as [Details!1!FirstName],LastName as [Details!1!LastName],PhoneNumber as [Details!1!PhoneNumber]
From Person.Person Left Outer Join Person.PersonPhone on Person.BusinessEntityID=PersonPhone.BusinessEntityID
Where Person.BusinessEntityID<6
For XML Explicit)
--Verify the data
Select * from @a
--query to retrieve the data in the xml column
Select Name.value('@FirstName','varchar(20)') as [FirstName],
Name.value('@LastName','varchar(20)') as [LastName],
Name.value('@PhoneNumber','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/Details') as Person(Name)

Output :

Querying XML data in SQL – XML Auto

Below code shows 1.retrieving the data using XML auto and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example.
--Generates XML data and inserts into the XML column.
Declare @a table(sno int,sname xml)
Insert into @a(sno,sname)
Select 1,(Select FirstName,LastName,PhoneNumber
from Person.Person Left Outer Join Person.PersonPhone
on Person.BusinessEntityID=PersonPhone.BusinessEntityID
where Person.BusinessEntityID<6 for XML Auto)

--Verify the data
Select * from @a

--TSQL Query to get the output from the XML column
Select Sno,Name.value('@FirstName','varchar(20)') as [FirstName],
Name.value('@LastName[1]','varchar(20)') as [LastName],
Name.value('(./Person.PersonPhone/@PhoneNumber)[1]','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/Person.Person') as Person(Name)

Output :

Querying XML data in SQL – XML Raw

Below code shows 1.retrieving the data using XML raw and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example,
--Generates XML data and inserts into the XML column.
Declare @a table(sno int,sname xml)
Insert into @a(sno,sname)
Select 1,(Select FirstName,LastName,PhoneNumber
from Person.Person Left Outer Join Person.PersonPhone
on Person.BusinessEntityID=PersonPhone.BusinessEntityID
where Person.BusinessEntityID<6 for XML RAW('PersonDetails'))

--Verify the data
Select * from @a

--TSQL Query to get the output from the XML column
Select Sno,Name.value('(@FirstName)[1]','varchar(20)') as [FirstName],
Name.value('(@LastName)[1]','varchar(20)') as [LastName],
Name.value('(@PhoneNumber)[1]','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/PersonDetails') as Person(Name)

Output :

Querying XML data in SQL – XML Path

Below code shows 1.retrieving the data using XML path and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example.
--Generates XML data and inserts into the XML column.
declare @a table(sno int,sname xml)
Insert into @a(sno,sname)
Select 1,(Select FirstName,LastName,PhoneNumber
from Person.Person Left Outer Join Person.PersonPhone
on Person.BusinessEntityID=PersonPhone.BusinessEntityID
where Person.BusinessEntityID<6 for XML Path('PersonDetails'))

--Verify the data
Select * from @a

--TSQL Query to get the output from the XML column
Select sno,Person.Name.value('./FirstName[1]','varchar(20)') as [FirstName],
Person.Name.value('./LastName[1]','varchar(20)') as [LastName],
Person.Name.value('./PhoneNumber[1]','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/PersonDetails') as Person(Name)

Output: