SQL Analytic Functions new in SQL Server 2012 for T-SQL Developers

Microsoft SQL Server 2012 introduces Analytic Functions for T-SQL developers with SQL Server Denali CTP3 version. Analytic functions have been in Oracle database platform for PL/SQL programmers for a while. Now analytic functions in SQL Server is a new enhancement for TSQL programmers with SQL Server 2012, aka Denali.

Please note that before the official name of SQL Server Denali is announced, we were expecting Denali to be named as SQL Server 2011 or SQL11.

What is the list of SQL Server Analytic Functions available

With SQL Server 2012 enhancements in T-SQL programming and programmability, the following 8 analytic functions have been added to the T-SQL engine for use of SQL programmers.

CUME_DIST analytic function
FIRST_VALUE analytic function
LAST_VALUE analytic function
LAG analytic function
LEAD analytic function
PERCENTILE_CONT analytic function
PERCENTILE_DISC analytic function
PERCENTILE_RANK analytic function

For SQL Server First_Value analytical function sample, T-SQL developers can refer to First_Value SQL Analytic Function in SQL Server 2012 T-SQL tutorial.

For SQL Last_Value() analytic function samples, please check the T-SQL tutorial Last_Value SQL Analytic Function in SQL Server 2012 T-SQL

SQL Lead() function is one of my favorite new analytic functions in SQL Server 2012. The reason is SQL Server Lead() function can be used to return a value in the next row. T-SQL developers can use Lead() analytic function to return the next value with the current row. Just have a look at the SQL sample codes here SQL LEAD() Function in SQL Server 2012 for Calculating Next Value

Just like Lead() function to calculate Next values, the new SQL analytic functionLag() can be used to calculate previous values in an ordered rows in SQL Server 2012 by T-SQL developers. Here is SQL Lag() function samples at SQL LAG() Function in SQL Server 2012 for Calculating Previous Values