This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.
Lessons:
Creating Windows with OVER
Exploring Window Functions
Lab : Using Window Ranking, Offset and Aggregate Functions
After completing this module, you will be able to:
Describe the benefits to using window functions.
Restrict window functions to rows defined in an OVER clause, including partitions and frames.
Write queries which use window functions to operate on a window of rows and return ranking, aggregation and offset comparison results.