What is the Difference between ROW_NUMBER(), RANK() and DENSE_RANK()?

In this article we will explain the difference between these three functions

All of these functions are used to calculate RowID for the result set.

Row_number:
This function always assign a unique id to each row returned from the query.
Consider the following query

DECLARE @Table1 TABLE (
      ValueName varchar(20)
)

INSERT INTO @Table1 (ValueName)
      VALUES ('Apple'),('Apple'),('Apple'),('Bat'),('Bat'),('Cat'),('Cat');

SELECT
      ValueName,
      ROW_NUMBER() OVER (ORDER BY ValueName) AS 'RowID'
FROM
      @Table1;    

Noted that every row has unique ID

Rank:
This function always assign a unique number to each distinct row ,but it leaves a gap between the groups
Let us understand with following query.

DECLARE @Table1 TABLE (
      ValueName varchar(20)
)

INSERT INTO @Table1 (ValueName)
      VALUES ('Apple'),('Apple'),('Apple'),('Bat'),('Bat'),('Cat'),('Cat');

  SELECT
      ValueName,
      Rank() OVER (ORDER BY ValueName) AS 'RowID'
FROM
      @Table1;

Noted we can see, row id is unique for each distinct value, but with a gap.

This gap represents number of occurrence. For example: value ‘Apple’ is repeated thrice and has rank ‘1’, the next rank will be 1+3=4. Same with the next value 4+2=6 and so on.

Dense_Rank:
This function is similar to Rank() but only difference that this will not leave gap between groups

Let us understand with following query.

DECLARE @Table1 TABLE (
      ValueName varchar(20)
)

INSERT INTO @Table1 (ValueName)
      VALUES ('Apple'),('Apple'),('Apple'),('Bat'),('Bat'),('Cat'),('Cat');

   SELECT
      ValueName,
      DENSE_RANK() OVER (ORDER BY ValueName) AS 'RowID'
FROM
      @Table1;

So it is clear that it generates a unique id for each group and without repetition.