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

SQL
7 Comments

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 this function was 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 also helps performance as well as readability.

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

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:

FunctionSQLAuthorityMSDN
CUME_DISTCUME_DISTCUME_DIST
FIRST_VALUEFIRST_VALUEFIRST_VALUE
LAST_VALUELAST_VALUELAST_VALUE
LEADLEADLEAD
LAGLAGLAG
PERCENTILE_CONTPERCENTILE_CONTPERCENTILE_CONT
PERCENTILE_DISCPERCENTILE_DISCPERCENTILE_DISC
PERCENT_RANKPERCENT_RANKPERCENT_RANK

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

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

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Introduction to PERCENTILE_DISC() – Analytic Functions Introduced in SQL Server 2012
Next Post
SQL SERVER – Solution to Puzzle – Simulate LEAD() and LAG() without Using SQL Server 2012 Analytic Function

Related Posts

7 Comments. Leave new

  • This page is in my bookmarks now …:)… Great series of SQL Server 2012 analytic functions.

    Regards,
    P.Anish Shenoy.

    Reply
  • Chirag Satasiya
    November 23, 2011 9:21 pm

    Hi pinal sir,
    I am expecting this post and here it is.

    Thank you for sharing.

    Regard$
    Chirag Satasiya

    Reply
  • Thanks Pinal, I am looking for the exact post…

    Appreciate your efforts.

    Regards,
    Suman Balguri

    Reply
  • hi sir i want to know how to count coloumns reapting the same values in table please help me out its attendence table and i want to count number of leave,prestent ,casual leave and all

    Reply
  • Hello Pinal,

    There is a commercial product which provides hundreds of analytic functions for SQL Server 2005, 2008 and 2012 – financial, statistical, mathematical etc. They are implemented as .NET SQL-CLR functions (permission level:safe), modeled mainly after the analytic functions found in Excel, but also from those found in R, Matlab, and Bloomberg.

    It is called XLeratorDB and can be found at westclintech.com

    Reply
  • thanks sweete

    Reply

Leave a Reply

Menu