Content area
Full text
An introduction to the concept of windowing
SQL Server 2012 (formerly code-named SQL Server Denali) introduces several important T-SQL prog ram m ability features; this article focuses on one of those features - window functions. SQL Server 2005 was the first milestone in supporting window functions; it introduced window ranking functions (ROW_NUMBER. RANK, DENSE^RANK. and NTlLE), as well as limited support for window aggregate functions - only with a window partition clause. SQL Server 2012 enhances support for window aggregate functions by introducing window order and frame clauses, support for offset functions (LAG, LEAD. FIRST_VALUE, and LAST_VALUE), and support for window distribution functions (PERCENT_RANK. CUME_DIST, PERCENTILE^DISC. and PERCENTILE_CONT).
Window functions are the best thing Io happen since sliced bread: therefore, I'm going to spend more than one article on the topic. In fact, I just finished writing an entire book on the topic - it's that big! This month I'll introduce the concept of windowing, describe the elements involved in window specifications, and cover window aggregate functions. In later articles I'll describe window offset functions, window distribution functions, and optimization of window functions.
To be able to run the examples from this series, you need to use SQL Server Denali CTP3 or later, as well as a sample database called TSQL2012. You can download SQL Server Denali CTP3 from www.microsoft.com/betaexperience/pd/SQLD CTP3CTA/cnus/default.aspx. You can download the sample database TSQL2012 from tsql.solidq .com/books/source_eodc/TSQL2012.zip.
Definition
A window function is a function that's applied to a set of rows defined by a window descriptor and returns a single value for each row from the underlying query. The purpose of the window descriptor is to define the sel of rows that the function should apply to. You provide the window specification using a clause called OVER that you're probably familiar with from SQL Server 2005's window ranking and aggregate functions. Here's an example from SQL Server 2012, relying on new capabilities:
Figure 1 shows an abbreviated form of the query output.
Don't worry about not understanding the full window function's specification yet; we'll get to that later. I just wanted you to have the code in front of you as I discuss the concepts. For now. suffice it to say that the query returns detail elements from rows...