The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

Row_Number()

Syntax

 Row_Number() Over( partition by clause order by clause)

This function will assign a unique id to each row returned from the query.

DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      ROW_NUMBER() OVER ( partition by col_value ORDER BY Col_Value) AS 'RowID'
FROM     @Table;   

OUTPUT

Col_Value     RowID
   A                 1
   A                 2
   A                 3
   B                 1
   B                 2
   C                 1
   C                 2

SELECT
      Col_Value,
      ROW_NUMBER() OVER ( ORDER BY Col_Value) AS 'RowID'

FROM     @Table;  


Col_Value     RowID
   A                 1
   A                 2
   A                 3
   B                 4
   B                 5
   C                 6
   C                 7


Rank() 

Syntax
Rank() Over( partition by clause order by clause)

Same  as Row_Number() except  provide same rank for equal rows and also gap  between the different rank we can avoid using Dense_Rank() keyword

Dense_Rank()  Provide same rank for equal rows   function do not have gap alway given consective Rank value

Syntax

Dense_Rank() Over( partition by clause order by clause)