How to use Row_Number, Rank, Dense_Rank Functions in SQL Server

What is the Difference between Row_Number, Rank and Dense_Rank in SQL Server

Row_Number():
This function Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)

Rank():
This function Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the rows in the criteria. 

RANK () OVER ([< partition_by_clause >] < order_by_clause >)

Dense_Rank()

This function Returns the rank of rows within the partition of a result set, without any gaps in the ranking order. The rank of a row is one plus the number of distinct ranks that come before the row in the
criteria.

DENSE_RANK () OVER ([<partition_by_clause> ] < order_by_clause > )

NTILE()

This function Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

NTILE (integer_expression) OVER ([<partition_by_clause>] < order_by_clause >)

here:
<partition_by_clause>
This Divides the result set produced by the From clause into partitions to which the Row_Number/ Rank/ Dense_Rank/ Ntile function is applied.

<order_by_clause>
This Determines the order in which the Row_Number/ Rank/ Dense_Rank/ Ntile values are applied to the rows in a partition.

Lets Consider the following Table on which we apply these functions:

Example 1: Row_Number/Rank/Dense_Rank/NTile  with Order by Dept_Name:

SELECT [Emp_Id]
          ,[Emp_Name]
         ,[Dept_Name]
          ,[Basic_Sal]
          ,ROW_NUMBER() OVER ( ORDER BY Dept_Name) AS ROW_NUM
  ,RANK() OVER ( ORDER BY Dept_Name) AS RANK
  ,DENSE_RANK() OVER ( ORDER BY Dept_Name) AS DENSE_RANK
  ,NTILE(7) OVER ( ORDER BY Dept_Name) AS NTILE
  FROM [dbo].[Tbl_Emp_Dtls]

GO

Output:

Notes:

Here Row_Num( ) assigns the Row Numbers in sequential order for each row

RANK( ) assigns the same Rank Number 1 (6 times) for the Dept_Name "BI&A", next when the Dept_Name changes, it  starts the Rank Numbers as 6+1...

Dense_Rank( ) assigns the same Rank Number 1 (6 times) for the Dept_Name "BI&A", next when the Dept_Name changes, it starts the Rank Numbers as 2 without breaking the ranking order.

NTile(7) distributes the rows into a specified number (7 here) of groups.

Example 2: Row_Number/Rank/Dense_Rank/NTile with Partition by Dept_Name:

In the below example the Row_Number/Rank/Dense_Rank/NTile will work in the same way as above but they are Partitioned by Dept_Name here.

In this example though we use NTILE (7), it distributes the rows in an ordered partition into only 6 groups, as 7th row is not available in that partition.

SELECT [Emp_Id]
   ,[Emp_Name]
   ,[Dept_Name]
   ,[Basic_Sal]
   ,ROW_NUMBER() OVER (PARTITION BY Dept_Name ORDER BY Basic_Sal) 
                AS ROW_NUM
   ,RANK() OVER (PARTITION BY Dept_Name ORDER BY Basic_Sal) AS RANK
   ,DENSE_RANK() OVER (PARTITION BY Dept_Name ORDER BY Basic_Sal) 
                    AS  DENSE_RANK
  ,NTILE(7) OVER (PARTITION BY Dept_Name ORDER BY Basic_Sal) AS NTILE
  FROM [dbo].[Tbl_Emp_Dtls]

GO

Output:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------