Yesterday, I spoke to the Steel City SQL User group on T-SQL window functions. Below is a short summary and links to the sample scripts and database.
The T-SQL window functions came about in SQL Server 2005, but you really want SQL Server 2012, because many functions were added and also because support for PARTITION BY was introduced only in 2012.
The basic script below does a good job demonstrating each function with very small data sets in temporary tables. This makes it easy to examine the output. For larger examples, I used two real-world data sets: domestic flight data from October and November 2014 (about 1 million rows) and course section data from Troy University. The flight data is available for download (caution: large file), but I can’t make the course section data available.
The functions that I focused on during my presentation were
- ROW_NUMBER
- RANK, PERCENT_RANK and DENSE_RANK
- LAG and LEAD
I briefly showed the other window functions also, like CUME_DIST, PERCENTILE_CONT, etc. Some of the sample scripts also include turning SUM and COUNT into window functions instead of aggregate functions (i.e no GROUP BY required).
Here are the downloads (hosted on OneDrive):
- Basic sample script, with temp table definitions included
- Flight data scripts
- Flight data SQL BAK file (just under 13 MB in a 7zip self-extracting archive)
Sven Aelterman 