Difference between ROW_NUMBER(),RANK(),DENSE_RANK

Difference between ROW_NUMBER(),RANK(),DENSE_RANK

Let us assume, i have a emp table columns ename,sal. The emp table shows below.

ENAME	SAL
SMITH	800
ALLEN	1600
WARD	1250
JONES	2975
MARTIN	1250
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000
TURNER	1500
ADAMS	1100
JAMES	950
FORD	3000
MILLER	1300

Now lets query the table to get the salaries of all employee name with their salaries in descending order.

Query like this.

SELECT ENAME,SAL,ROW_NUMBER() OVER(ORDER BY SAL DESC) ROW_NUMBER, RANK() OVER(ORDER BY SAL DESC)RANK, DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK FROM EMP

Out put

ENAME	SAL	ROW_NUMBER	RANK	DENSE_RANK
KING	5000	1	1	1
SCOTT	3000	2	2	2
FORD	3000	3	2	2
JONES	2975	4	4	3
BLAKE	2850	5	5	4
CLARK	2450	6	6	5
ALLEN	1600	7	7	6
TURNER	1500	8	8	7
MILLER	1300	9	9	8
WARD	1250	10	10	9
MARTIN	1250	11	10	9
ADAMS	1100	12	12	10
JAMES	950	13	13	11
SMITH	800	14	14	12

So question is which one to use? 

Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don't leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)