Unicque Row Num and Same Rank in Sql server

 Use Of  Row_Number() ,Rank() function , 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 'Row ID'
FROM
      @Table;    

After executing it, we will get:

 Col_Value  Row ID
           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 'Row ID'
FROM
      @Table;

After executing it, we will get:

Col_Value Row ID
     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()

 his 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 'Row ID'
FROM
      @Table;

 After executing it, we will get:

Col_Value Row ID
        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 our need. For example: Row_Number() can be used if we want to skip and fetch some records. Take a look at this query:
	Similarly, we can use Dense_Rank()when we need to calculate  row id with Select Distinct.
	SELECT DISTINCT Col_Value, DENSE_RANK() OVER (ORDER BY Col_Value) AS 'Row ID' FROM @Table; Will return:-
	Col_Value Row ID         A     1         B     2         C     3