Microsoft SQL Server 2012 Bible

Windowing and Ranking

T-SQLs windowing and ranking function provides developers with the ability to shed new light on the same old data. Windowing as it relates to SQL Server is not related in any way to Microsoft Windows. Instead, it refers to ways of working with subsets of data. Windowing, using the OVER() clause, provides a new perspective of the data. The ranking functions use the new perspective to provide additional ways for manipulating the data.

Although these two methods are similar to aggregating data, they should be thought of as a different technique and technology. This is because they work within an independent sort order separate from the query's sort order.

Windowing

As previously stated, windowing in a T-SQL query creates a new perspective of the data. Even though windowing and ranking are placed together in the T-SQL syntax, the window must be established prior to the ranking functions.

The OVER() Clause

The OVER() clause creates a new window on the data. Consider it as a new perspective, or independent ordering of the rows, which may or may not be the same as the sort order of the ORDER BY clause. In a way, the windowing capability creates an alternative flow to the query with its own sort order and ranking functions.

The complete syntax is OVER(ORDER BY columns). The columns may be any available column or expression, just like the ORDER BY clause; but unlike the ORDER BY clause, the OVER() clause won't accept a column ordinal position, for example, 1, 2. Also, ...