SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority

SQL Server 2012 (RC0 available here) has introduced new analytic functions. These functions were long awaited and I am glad that they are now here. Before, when any of these functions were needed, people used to write long T-SQL code to simulate these functions. But now, there’s no need of doing so. Having available native function helps performance as well as readability.

In the last few days, I have written many articles on this subject on my blog. The goal was to make these complex analytic functions easy to understand and make them widely accepted. As these new functions are available and as awareness spreads about them, we should start using these new functions. Here is a quick list of the new functions and relevant MSDN sites:

Function	SQLAuthority	MSDN
CUME_DIST	CUME_DIST	CUME_DIST
FIRST_VALUE	FIRST_VALUE	FIRST_VALUE
LAST_VALUE	LAST_VALUE	LAST_VALUE
LEAD	LEAD	LEAD
LAG	LAG	LAG
PERCENTILE_CONT	PERCENTILE_CONT	PERCENTILE_CONT
PERCENTILE_DISC	PERCENTILE_DISC	PERCENTILE_DISC
PERCENT_RANK	PERCENT_RANK	PERCENT_RANK

I also enjoyed three different puzzles during the course of this series which gave a clear idea about the SQL Server 2012 analytic functions.

• SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY
• SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using LEAD and LAG
• SQL SERVER – Puzzle to Win Print Book – Explain Value of PERCENTILE_CONT() Using Simple Example

This series will always be my dear series as during this series, I went through a very unique experience of my book going out of stock and becoming available after 48 hours.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Filed under: CodeProject, Pinal Dave, PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology