♠This post is the first post in a series discussing the new Analytic functions in SQL Server “Denali”, CTP3, and it will also serve as the landing page for this series.
(The information in this article may change when this version is released to RTM; however since the changes described adhere to the ANSI specification, I don’t expect any changes.)
Analytic Functions
It’s hard to describe the overall purpose of the analytic functions better than what Books Online (BOL) does: “Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.”
SQL Server “Denali” adds eight analytic functions, all of which require the use of the OVER clause (please see my article The OVER Clause enhancements in SQL Server code named “Denali”, CTP3 for more details of the OVER clause and how it has been enhanced in SQL Server “Denali”).
The Analytic Functions series
The first post in the series covers the LAG and LEAD functions.
The second post in the series covers the FIRST_VALUE and LAST_VALUE functions.
The third post in the series covers the CUME_DIST and PERCENT_RANK functions.
The final post in the series covers the PERCENTILE_CONT and PERCENTILE_DISC functions.
Support for the OVER clause by function
| FUNCTION | OVER Clause Usage | PARTITION BY | ORDER BY | ROWS/RANGE |
|---|---|---|---|---|
| CUME_DIST | R | O | R | X |
| FIRST_VALUE | R | O | R | O |
| LAG | R | O | R | O |
| LAST_VALUE | R | O | R | O |
| LEAD | R | O | R | X |
| PERCENTILE_CONT | R | O | X | X |
| PERCENTILE_DISC | R | O | X | X |
| PERCENT_RANK | R | O | R | X |
O=Optional, R=Required, X=Not Supported
