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