row_number(),rank(),dense_rank().

Row_Number()

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

Consider the following 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 (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;   
After executing it, we will get:

Col_Value RowID
A 1
A 2
A 3
B 4
B 5
C 6
C 7
As we notice, each and every row has a unique ID.

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().


SELECT
      Col_Value,
      Rank() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;
This query will return:

Col_Value RowID
A 1
A 1
A 1
B 4
B 4
C 6
C 6
As we can see, rowid is unique for each distinct value, but with a gap. What is this gap?

This gap represents number of occurrence. For example: value ‘a’ 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 with only difference, this will not leave gaps between groups.

So if we use the same query used for Rank, then:

SELECT
      Col_Value,
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;
We will get:

Col_Value RowID
A 1
A 1
A 1
B 2
B 2
C 3
C 3
So it is clear that it generates a unique id for each group and without repetition.

As we are clear now what these functions do, we can use them in different scenarios based on our need