A short script to demonstrate some of the new analytic functions in SQL Server 2012.

— Begin Script

CREATE TABLE TestTable (
RecordId INT NOT NULL IDENTITY(1, 1),
DisplayName VARCHAR(20) NOT NULL
)

GO

INSERT INTO TestTable(DisplayName) VALUES (‘Record 1’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 2’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 3’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 4’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 5’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 6’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 7’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 8’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 9’)
INSERT INTO TestTable(DisplayName) VALUES (‘Record 10’)
GO

SELECT RecordId,
FIRST_VALUE(RecordId) OVER (ORDER BY RecordId ASC) AS FirstValue,
LAST_VALUE(RecordId) OVER (ORDER BY RecordId ASC) AS LastValue,
LEAD(RecordId) OVER (ORDER BY RecordId ASC) AS LeadValue,
LAG(RecordId) OVER (ORDER BY RecordId ASC) AS LagValue,
CUME_DIST() OVER (ORDER BY RecordId ASC) AS CumeDistValue,
PERCENT_RANK() OVER (ORDER BY RecordId ASC) AS PercentRankValue
FROM TestTable
GO
— End script

— Results

Record Id First Value Last Value Lead Value Lag Value Cume Dist Value Percent Rank Value
1 1 1 2 NULL 0.1 0
2 1 2 3 1 0.2 0.1111111111
3 1 3 4 2 0.3 0.2222222222
4 1 4 5 3 0.4 0.3333333333
5 1 5 6 4 0.5 0.4444444444
6 1 6 7 5 0.6 0.5555555556
7 1 7 8 6 0.7 0.6666666667
8

1 8 9 7 0.8 0.7777777778
9 1 9 10 8 0.9 0.8888888889
10 1 10 NULL 9 1 1

All of these functions take the OVER clause to establish the order in which to process results.

FIRST_VALUE – The value of the first record in the result set.
LAST_VALUE – The value of the current record in the result set.
LEAD – The value of the previous record in the result set.
LAG – The value of the next record in the result set.
CUME_DIST – cumulative distribution – From BOL ” the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows evaluated in the partition or query result set.”
PERCENT_RANK – The relative rank.