Dense_Rank Function in SQL server

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row. The RANK and DENSE_RANK functions are quite different, however, since instead of just assigning sequential numbers, they actually rank the rows based on the ORDER BY expression

There is also a difference between RANK and DENSE_RANK. The RANK function returns the rank of the current row compared to all the rows of the partition. The DENSE_RANK function returns the unique rank of the current row in the partition. RANK says how many rows before the current one, and DENSE_RANK says how many different values come before the current value. Another way to think about this is that DENSE_RANK doesn’t waste any numbers, while RANK skips numbers.

Syntax

DENSE_RANK() OVER([PARTITION BY <col1>[,<col2>,...<n>]] ORDER BY <col3>[,<col4>])

See the example

Without partition by clause

SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd') AS OrderDate, SalesOrderID, Dense_RANK() OVER(ORDER BY CustomerID) AS Rank_Number FROM Sales.SalesOrderHeader;

See the output    

With partition by clause

SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd') AS OrderDate, SalesOrderID, Dense_RANK() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS Rank_Number FROM Sales.SalesOrderHeader;

See the output