Microsoft SQL Server 2012 Bible

Ranking Functions

The windowing capability (the OVER() clause) by itself doesn't create any query output columns; that's where the ranking functions come into play:

• row_number
• rank
• dense_rank
• ntile

Just to be explicit, the ranking functions all require the windowing function. These functions add a rank to each row of the subset of data. The ranking of the rows depends on which function that is specified.

You can use all the normal aggregate functions — SUM(), MIN(), MAX(), COUNT(*), and so on — as ranking functions.

Row number() Function

The ROW_NUMBER() function generates an on-the-fly auto-incrementing integer according to the sort order of the OVER() clause. It's similar to Oracle's RowNum column.

The row number function simply numbers the rows in the query result; there's absolutely no correlation with any physical address or absolute row number. This is important because in a relational database, row position, number, and order have no meaning. It also means that as rows are added or deleted from the underlying data source, the row numbers for the query results will change. In addition, if there are sets of rows with the same values in all ordering columns, then their order is undefined, so their row numbers may change between two executions even if the underlying data does not change.

One common practical use of the ROW_NUMBER() function is to filter by the row number values for pagination. For example, a query that easily produces rows 21–40 would be useful for returning ...