Difference Between Rank(), Dense_Rank() And Row_Number() In SQL

Introduction

In this post we will learn what is the difference between Rank(), Dense_Rank() And Row_Number() and when do we need this.

Previous Updates
In previous articles we have what is Pivot table in SQl. Difference between  Scope_Identity(), @@Identity and Ident_Current . Stuff and Replace in SQl. Temp table and table variable difference and when to use. Sequence in sql server with example.Group By in SQL Server and its use. If Else and Case statement  , While Loop . What is Cursor in sql and use of cursor.

Practice
For to understand all three SQL function first we need a table and few records for that table. Here i am sharing my table structure with you :-

CREATE TABLE [dbo].[TestTable] (     [ID]   INT          IDENTITY (1, 1) NOT NULL,     [Name] VARCHAR (50) NULL,     [Marks] INT NOT NULL );

Now Here is my DataScript for values insertion

INSERT INTO TestTable VALUES( 'Jack' ,90) INSERT INTO TestTable VALUES( 'Nicks' ,70) INSERT INTO TestTable VALUES( 'GN' ,80) INSERT INTO TestTable VALUES( 'Shiv' ,65)

How to use Row_Number(), Rank() and Dense_Rank() In Sql

Now its time to write the all three sql function to use them and know what is the use of these Rank(), Dense_Rank() and Row_Number().

SELECT Name,    Marks, ROW_NUMBER () OVER (ORDER BY Marks DESC)  as ROW_NUMBER, RANK () OVER (ORDER BY Marks DESC)  as RANK, DENSE_RANK () OVER (ORDER BY Marks DESC)  as DENSE_RANK FROM dbo.TestTable

After running this expression  you will get the following output

Little bit confusing now. After seeing the result we find all three function returning the same values 1,2,3,4 . So whats the main difference in this. 
Now i am duplication few records for better understanding on this topic. 

In Select table statement you can see few records with same Marks like 90 and 70 and after that when we perform the same query expression on Marks then we find the output which we really wants.

Explanation Based Upon the Result

Row_Number()
It plays a important role in sql server .Row_Number() returns a unique number for each row starting with 1. 

When we have large records and we have to get few records related to some row then we use this function.

Rank()
Rank() function will assign a unique value to each distinct row but it leaves a gap between the group records.

Means it will display the same Rank for all duplicate records (just like Jack and GN  Rank is 1 and Nicks , Mark, Roman Rank is 3) .
It leaves a gap between the group records means here 'Jack' and 'GN' has same Rank 1 and after this it assigns rank 3 to Nicks, implies it count no of occurrences of Marks '90' and it gets count 2 then the next Rank will be Occurrence Count + 1 which is '3'.

Dense_Rank()
It is similar to Rank function but it will not leave a gap between the group elements. In other words w e can say that Dense_Rank() function will assign a unique value to each distinct row.