Improved Support for Window Functions in SQL Server 2012 CTP3

For several years now the SQL Server community kept asking for more complete support for window functions in SQL Server. In a past blog entry, I explained what window functions are and what was missing in SQL Server so far. More complete support for window functions was among the most requested SQL Server improvements by SQL Server MVPs and the SQL Server community in general, and Microsoft listened…

SQL Server 2012 (formerly code-named Denali) CTP 3 adds more complete support for window functions, including enhanced support for window aggregate functions, as well as support for window offset and window distribution functions. In this entry I’m just going to highlight the enhancements. In future articles in my column I’m going to get into the details. The topic is so big that I wrote an entire book dedicated to it. The book will be published in a few months, probably after SQL Server Denali RTMs.

SQL Server Denali improves support for window aggregate functions by adding a window order clause and a window frame clause. This means that you can now restrict a subset of rows within the window partition. SQL Server supports a ROWS option that allows you to frame rows based on an offset from the current row in terms of number of rows. SQL Server also adds limited support for the RANGE option, which allows framing rows based on an offset from the current row in terms of values.

As an example for using the ROWS option, the following query issued against the sample database TSQL2012, computes the running total quantity for each employee and order month:

SELECT empid, ordermonth, qty,

  SUM(qty) OVER(PARTITION BY empid

                ORDER BY ordermonth

                ROWS BETWEEN UNBOUNDED PRECEDING

                         AND CURRENT ROW) AS runqty

FROM Sales.EmpOrders;

SQL Server Denali adds support for window offset functions LAG and LEAD which allow returning elements from a preceding or a following row. It also supports the FIRST_VALUE and LAST_VALUE functions which allow returning elements from the first or last row in a window frame. As an example, the following query returns for each customer order the value of the current, previous and next orders:

SELECT custid, orderdate, orderid, val,

  LAG(val)  OVER(PARTITION BY custid

                 ORDER BY orderdate, orderid) AS prevval,

  LEAD(val) OVER(PARTITION BY custid

                 ORDER BY orderdate, orderid) AS nextval

FROM Sales.OrderValues;

SQL Server Denali also introduces support for window distribution functions. It adds window rank distribution functions PERCENT_RANK (for percentile rank) and CUME_DIST (for cumulative distribution). It also adds window inverse distribution functions PERCENTILE_DISC (percentile using discrete distribution model) and PERCENTILE_CONT (percentile using continuous distribution model). The last two are implemented as window functions and not as grouped ordered set functions.

As an example, the following query computes the percentile rank and cumulative distribution of student scores per test:

SELECT testid, studentid, score,

  PERCENT_RANK() OVER(PARTITION BY testid ORDER BY score) AS percentrank,

  CUME_DIST()    OVER(PARTITION BY testid ORDER BY score) AS cumedist

FROM Stats.Scores;

As mentioned, this entry purpose was only to highlight the windowing improvements in SQL Server Denali. I will provide much more detail in future articles in my column and in a book dedicated to the topic.

Cheers,

BG