Difference Between Rank, Dense_Rank and Row_Number
Rank, Dense_Rank and Row_Number are ranking functions in SQL Server.
1. The difference between rank/dense_rank and row_number is that, row_number is not deterministic when the order-by list is not unique. Rank and dense_rank are always deterministic, that is, the same ranking values are assigned to rows with same sort values.
As per SQL Server 2016 and later all three i.e. Rank, Dense_Rank and Row_Number are Nondeterministic functions.
2. The difference between rank and dense_rank is that rank might have gaps in the ranking values, but allows you to know how many rows have lower sort values. Dense_rank values have no gaps.
Look at the results below - rank values are 1, 1, 1, 4, 5 (have a gap) and results of dense_rank 1, 1, 1, 2, 3 (have no gap). [Source: Inside Microsoft SQL Server 2008 T-SQL Querying: T-SQL Querying, Microsoft SQL Server 2012 Unleashed]
3. The difference between rank and dense_rank is that rank indicates how many rows have a lower ordering value, whereas dense_rank indicates how many distinct ordering values are lower. For example, a rank of 9 indicates eight rows with lower values. A dense_rank of 9 indicates eight distinct lower values. [Source: Microsoft SQL Server Interview Questions By Chandan Sinha]
4. Rank counts each tie as a ranked row. Dense_rank handles ties differently. Tied rows only consume a single value in the ranking, so the next rank is the next place in the ranking order. No ranks are skipped. [Source: Microsoft SQL Server 2008 Bible By Paul Nielsen, Uttam Parui]
Very simple example depicting the difference in results of rank, dense_rank and row_number
CREATE TABLE Z2
(
Name varchar(255)
);
insert into Z2 values ('a');
insert into Z2 values ('a');
insert into Z2 values ('a');
insert into Z2 values ('b');
insert into Z2 values ('c');
Select * from Z2
| Name |
|---|
| a |
| a |
| a |
| b |
| c |
SELECT *, Rank() over (ORDER BY Name ASC) AS rank_example FROM Z2;
SELECT *, Dense_rank() over (ORDER BY Name ASC) AS dense_rank_example FROM Z2;
SELECT *, Row_number() over (ORDER BY Name ASC) AS row_number_example FROM Z2;
| Name | rank_example |
|---|---|
| a | 1 |
| a | 1 |
| a | 1 |
| b | 4 |
| c | 5 |
| Name | dense_rank_example |
|---|---|
| a | 1 |
| a | 1 |
| a | 1 |
| b | 2 |
| c | 3 |
| Name | row_number_example |
|---|---|
| a | 1 |
| a | 2 |
| a | 3 |
| b | 4 |
| c | 5 |