How to Use Microsoft SQL Server 2012's Window Functions

SQL Server 2012 (formerly code-named SQL Server Denali) introduces several important T-SQL programmability 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 NTILE), 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).