This is the third hour of a three hour set of presentations on intermediate T-SQL techniques and features.
This presentation covers the window functions that are available in SQL Server. Some of these functions were introduced in SQL Server 2005 and some in SQL Server 2015. We start with a brief introduction to what a window is, how a partition works, and then look at ordering and framing of rows. Then we cover the following in demonstrations:
RANK(), DENSE_RANK(), and NTILE()
The OVER clause and windowing in SQL Server 2012
Using PARTITION BY and ORDER BY in the OVER clause
Default framing with RANGE and how ROWS are different
This covers a lot of information in an hour, but I try to use small data sets to explain what is happening with the window functions and how they can improve the performance, as well as simplify the code, for a number of common queries.