SQL Server 2005 Features - Ranking functions

Among many of the new features provided by SQL Server 2005 are its ranking functions. They ease the numbering of query results based on the sort criteria. Ranking Functions group certain rows within a result set into partitions and return a ranking value for each row in a partition.

They return a value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows.

Ranking functions are nondeterministic. i.e. you cant guess which row will get what rank if the sort criteria (columns in the Order By clause) are not unique.

Row numbers are deterministic when the sorting criteria are unique (e.g., orderid) — as long as the query’s input (the table) doesn’t change, the query’s output (the specific row numbers) will be the same.

The ranking functions offered by SQL Server 2005 are

1. ROW_NUMBER()
2. RANK()
3. DENSE_RANK()
4. NTILE()