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

Introduction

 
In this article we will learn about some SQL functions Row_Number() ,Rank(), and Dense_Rank() and the difference between them.
 

Creating a table in SQL Server

 
Here I have an Employe table, the following is the sample data for the Employe Table.
 
 

ROW_NUMBER() Function without Partition By clause

 
Row_number plays a very important role in SQL server. Row_Number function can help to perform more complex ordering of row in the report format that allow the over clause in SQL standard.
 
Syntax
 
ROW_NUMBER () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
 
Here we will play with the above Employe table. Let's see how we can use Row_Number() function.
 
Over specified the order of the row and Order by sort order for the record. By default order by sort in ascending order.
 
Example
  1. select *, ROW_NUMBER() over(order by EmpName) as rownumber from Employe  
The following is the output of the above query.
 
ROW_NUMBER() Function without Partition By clause 
 

ROW_NUMBER() Function with Partition By clause

 
If we want to add row number to each group, and it is reset for every group, let's take a look at the Employe table
 
Example
  1. Select *, ROW_NUMBER() over(partition by Empname order by Empname ) as rownumber from Employe  
The following is the OUTPUT of the above query
 
ROW_NUMBER() Function with Partition By clause 
 

Rank() Function in SQL Server

 
This function will assign a unique value to each distinct Row, but it leaves a group between the groups.
 
Example
  1. SELECT EmpName, EmpSalary ,rank() over(order by EmpName) as rankID from Employe  
The following is the OUTPUT of the above query.
 
Rank() Function in SQL Server  
 
Gap represents number of occurrence example - EmpName="atul" is repeated 3 times and has rank "1", the next rank will be 1+3=4 and same with the next value.
 

Dense_Rank() Function in SQL Server

 
Dense_Rank() Function is similar to Rank with only difference, this will not leave gaps between groups.
 
Example
  1. SELECT EmpName ,EmpSalary ,DENSE_RANK() over(order by EmpName) as DrankID from Employe  
The following is the OUTPUT of the above query:
 
Dense_Rank() Function in SQL Server
 

Summary

 
In this article we learned Row_Number() ,Rank(), and Dense_Rank() in SQL Server.