Difference Between Rank, Dense_Rank & Row_Number

Difference Between Rank, Dense_Rank & 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