New T-SQL Functions in SQL Server 2012

The latest version of SQL Server augments T-SQL with many new functions. In this section, we cover the 22 new functions (and 1 changed function) introduced in SQL Server 2012. We’ll start by covering the new T-SQL analytic functions, because they operate using the same windowing principles we were just discussing.

New Analytic Functions

Eight new analytic functions have been added to T-SQL. All of them work in conjunction with an ordered window defined with an associated ORDER BY clause that can be optionally partitioned with a PARTITION BY clause and framed with a BETWEEN clause. The new functions are:

  • FIRST_VALUE

  • LAST_VALUE

  • LAG

  • LEAD

  • PERCENT_RANK

  • CUME_DIST

  • PERCENTILE_CONT

  • PERCENTILE_DISC

In Example 2-32, the FIRST_VALUE, ...

Get Programming Microsoft® SQL Server® 2012 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial