Difference between rank and dense_rank and row_number

In this article I will show the difference between rank and dense_rank in sql server, difference between rank and row_number in sql server and the difference between dense_rank and row_number in sql server.

Assume you are writing a query on Employee table to get the salaries of all employees with their salary in descending order. Also i have to rank the employee according to their salary. 

Therefore I'll write a query like this :

SELECT names 

        , salary 

        ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER 

        ,rank () OVER (ORDER BY salary DESC) as RANK 

        ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK 

FROM Employee

Assume following output you will get

NAMES SALARY ROW_NUMBER RANK DENSE_RANK

S 10000 1      1 1

K 6000  2   2        2

L 5000  3         3 3

W 5000  4 3 3

G 4000  5         5 4

T 3000  6        6 5

If you notice interesting Names in the result are employee L, W and G. 

• Row_number assign different number to them. 
• Rank and Dense_rank both assign same rank to L and W. 
• But interesting thing is what RANK and DENSE_RANK assign to next row (i.e. G)? 
• Rank assign 5 to the next row, while dense_rank assign 4.

The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.