In SQL Server 2005, the RANK and DENSE RANK procedures were introduced.

Both of these procedures are used to return sequential integers starting at 1 based on the ORDER BY clause's sorting of rows.

Let's take a closer look at these functions with some examples before attempting to comprehend the differences between them.

SQL RANK Function

The Standard Query Language feature RANK can be thought of as a way for a programmer to categorise and assess a group of numbers.

This feature assigns a specific ranking to each number, and when the same number appears twice, the rank is also applied to those two numbers.

However, this feature is unique in that it always skips the following number when assigning a comparable rank to two or more digits.

Example 1 We have two departments, just like in the Employees table.

ID Name Gender City Dept Salary
1 Pandurengan Male Bangalore HR 31000
2 Chandra Female Delhi HR 4500O
3 Sakunthala Female Chennai IT 23000
4 Hanumanthan Male Chennai IT 11500

As a result of the Partition By Clause, all of the records will be divided into two groups.

Employees in the IT department have one partition, while those in the HR department have another.

The data is then sorted in each split depending on the Salary column. Except in the case of a tie, the RANK function assigns an integer sequence number starting at 1 to each record in each division.

In the event of a tie, the same rank is assigned and the ranking is skipped.

Now execute the following code and you will get the output as we discussed in the previous image.


                 ORDER BY
                     Salary DESC
                ) As [RANK]

In a summary, the RANK function returns an ascending unique number for each row beginning at 1 and for each partition.

When there are duplicates, all duplicate rows receive the same rank , but the next row after the duplicate rows receives the rank it would have had if there were no duplicates.

If there are duplicates, the RANK function skips them.


name dept salary rank
Sakunthala Administration 23000 1
Chandra A 4500 2
Hanumanthan Security 11500 1
Pandurengan Web Design 31000 1

Let's say there are two employees with the first highest compensation. There could be two business cases:

You must utilise the RANK function if your business requirement is to not produce any results for the SECOND highest salary.


The value of DENSE RANK differs somewhat from the previous one.

In the Standard Query Language, DENSE RANK is a feature that allows a programmer to categorise data without skipping any numbers, even when similar ranks are allowed.

This attribute serves as the element's primary distinguishing trait.

Example 1: On the Salary column, apply for the Order By Clause. As a result, it will assign a ranking based on the Salary column.

ID Name Gender City Dept Salary
1 Varshini Kutty Female Madurai Database 31000
2 Ranjani Mai Female Pune Administration 15000
3 Ramanathan Male Bangalore Web Design 42500
4 Padmavathi Female Mumbai Administration 8500


SELECT Name, Dept, Salary,
            DENSE_RANK() OVER (ORDER BY Salary DESC) AS [Rank]
FROM Employee;

Output: When you run the above SQL query, you'll get the following results. As you can see in the result, there is no partition, therefore all of the rows are allocated sequential numbers starting at 1, except when there is a tie.

name dept salary rank
Ramanathan Web Design 42500 1
Varshini Kutty Database 31000 2
Ranjani Mai Administration 15000 3
Padmavathi Administration 8500 4


Here we wil discuss the main differences between rank and dense rank procedure:-

Rank is a SQL function that assists programmers in categorising various sets of data. Another function that does a similar job but without skipping any numbers is the dense rank.
Distinct numerical ranks are assigned to different numbers in order to rank them When two numbers are numerically similar, they are assigned the same rank. The dense ranking is achieved by assigning different numerical ranks to different numbers while assigning similar numerical ranks to similar numbers. This process, however, does not skip any consecutive numbers.
Similar numbers are given the same rank in the ranking, however the next number after that rank is normally skipped. No number is scaped in dense ranking, and the rank follows a specific numerical order.
The goal of this function is to look at the specified rank of each and every row. The goal of this function is to examine the ranks of a single column rather than every row.
RANK is how it's written and read () It is spelled and read as DENSE RANK ()
When this function is run, similar ranks are assigned to similar numbers, and any numbers that follow that rank are skipped. When this function is run, similar ranks are assigned to similar numbers, but no numbers are skipped in the ranking.
When the PARTITION BY Clause is specified, the result set is partitioned based on the column specified in the Partition BY clause. If the PARTITION BY Clause is not specified, the DENSE RANK function will use the entire result set as a single partition and number it sequentially from 1 except where there is a tie.
The ranking is simple to derive from a large amount of data. The dense ranking is only useful for extracting data from a specific group of columns or rows.