Programming Microsoft® SQL Server® 2012

Windowing (OVER Clause) Enhancements

The first windowing capabilities appeared in SQL Server 2005 with the introduction of the OVER clause and a set of four ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE. In our discussion, the term “window” refers to the scope of visibility from one row in a result set relative to neighboring rows in the same result set. By default, OVER produces a single window over the entire result set, but its associated PARTITION BY clause lets you divide the result set up into multiple groups, each contained inside their own window. The row sequence within each window is determined by an associated ORDER BY clause, and based on this sequence, the ranking functions assign an accumulating value to the rows in ...