SQL Server 2011 “Denali” is quite rich for new functions which have really good practical use in T-SQL. I’ve read about another new functions in Denali’s Books Online. I am missing those functions, I will write about, really for years. They’re filled under “Analytical function” and they are close to sort of “ranking helper functions”. My Fantastic Four is LAG, LEAD, FIRST_VALUE and LAST_VALUE and if you’re T-SQL developer you will love them! Another five new functions are less attractive for me but they’re just there – CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC and PERCENT_RANK.

LAG and LEAD function s

They provide access to previous row (LAG) or subsequent row (LEAD)  in result set or partition. If they are combined with PARTITION BY clause within required OVER clause, it accesses previous/next row only from given partition. If there is previous/next row in result set/partition, both function returns null. These functions basically replace necessity for self-joins when you need to access previous/next row. Both functions provides access to rows based on given offset relative to current row.

FIRST_VALUE and LAST_VALUE functions

These functions are very similar to LAG and LEAD and do what is expected – provide access to first or last row in result set or partition. They must be combined with OVER clause as well, PARTITION BY clause is optional.

Here is very simple example of new analytical functions in action. SQL command groups employees’ lastname by department and use LAG, LEAD and FIRST_VALUE function results.

 SELECT 
 d.GroupName,
 b.LastName,
 LAG (LastName) OVER (PARTITION BY d.GroupName ORDER BY b.LastName) AS 'LAG - prev. value',
 LEAD (LastName) OVER (PARTITION BY d.GroupName ORDER BY b.LastName) AS 'LEAD - next. value',
 FIRST_VALUE (LastName) OVER (PARTITION BY d.GroupName ORDER BY b.LastName) AS 'FIRST_VALUE'
 FROM HumanResources.Employee e
    JOIN Person.Person b ON b.BusinessEntityID = e.BusinessEntityID
    JOIN HumanResources.EmployeeDepartmentHistory dh ON dh.BusinessEntityID = b.BusinessEntityID
    JOIN HumanResources.Department d ON d.DepartmentID = dh.DepartmentID
    ORDER BY d.GroupName, b.LastName

CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC and PERCENT_RANK

These function are pure statistical. You can use it when you need to calculate cumulative distribution or various types of percentile.

Tags: denali, LAST_VALUE, result set, set partition, SQL Server, sql server 2011, t-sql, value functions