SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority
- by pinaldave
SQL Server 2012 (RC0 Available here) has introduced new analytic functions. These functions were long awaited and I am glad that they are here. Previously when any of this function was needed people use to write long T-SQL code to simulate that and now no need of the same. Having available native function also helps performance as well readability.
In last few days I have written many articles on this subject on my blog. The goal was make these complex analytic functions easy to understand and make it widely accepted. As this new functions are available and as awareness spreads we should start using the new functions. Here is the quick list of the new function and relevant MSDN site.
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 clear idea to 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 be always my dear series as during this series I had went through 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: Pinal Dave, PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
