SQL Server RANK vs DENSE_RANK vs ROW_NUMBER: Window Function Guide

 

Introduction to Ranking Functions

SQL Server provides powerful window functions for assigning ranks to rows within a result set partition. RANK, DENSE_RANK, and ROW_NUMBER are essential for sorting, pagination, and top-N per group queries. While they seem similar, their behavior with ties and numbering gaps is critically different.

Core Concepts and Syntax

All three functions follow the same basic syntax but behave differently:

sql

FUNCTION_NAME() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

• PARTITION BY: Divides the result set into groups (optional)

• ORDER BY: Defines the logical order within each partition

1. ROW_NUMBER() Function

Assigns a unique sequential integer to each row within a partition, starting at 1. Never produces ties - even if rows have identical values.

Example Scenario: Numbering employees by salary within each department.

sql

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees VALUES
(1, 'John', 'Sales', 50000),
(2, 'Jane', 'Sales', 60000),
(3, 'Bob', 'Sales', 60000), -- Same salary as Jane
(4, 'Alice', 'IT', 70000),
(5, 'Charlie', 'IT', 65000);

SELECT 
    Department,
    Name,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) as RowNum
FROM Employees;

Result:

Department	Name	Salary	RowNum
IT	Alice	70000.00	1
IT	Charlie	65000.00	2
Sales	Jane	60000.00	1
Sales	Bob	60000.00	2
Sales	John	50000.00	3

Key Point: Even though Jane and Bob have identical salaries (60000), they get different row numbers.

2. RANK() Function

Assigns a unique rank to each distinct row, with the same rank for tied values, leaving gaps in the sequence.

Example: Ranking employees by salary across all departments.

sql

SELECT 
    Department,
    Name,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) as Rank
FROM Employees;

Result:

Department	Name	Salary	Rank
IT	Alice	70000.00	1
IT	Charlie	65000.00	2
Sales	Jane	60000.00	3
Sales	Bob	60000.00	3	-- Tie: same rank
Sales	John	50000.00	5	-- Gap: rank 4 is skipped

Key Point: Jane and Bob are tied for rank 3, so the next rank (John) becomes 5, not 4.

3. DENSE_RANK() Function

Assigns a unique rank to each distinct row, with the same rank for tied values, but without gaps in the ranking sequence.

Example: Dense ranking by salary.

sql

SELECT 
    Department,
    Name,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) as DenseRank
FROM Employees;

Result:

Department	Name	Salary	DenseRank
IT	Alice	70000.00	1
IT	Charlie	65000.00	2
Sales	Jane	60000.00	3
Sales	Bob	60000.00	3	-- Tie: same rank
Sales	John	50000.00	4	-- No gap: continues to 4

Key Point: The ranking continues sequentially without gaps after ties.

Comparative Analysis

Function	Ties Handling	Gaps	Sequence	Use Cases
ROW_NUMBER	Never - unique numbers	No gaps	Continuous	Pagination, removing duplicates, top-N per group
RANK	Same rank for ties	Gaps after ties	Non-continuous	Competition rankings, academic grading
DENSE_RANK	Same rank for ties	No gaps	Continuous	Statistical analysis, percentile calculations

Performance Considerations

1. Indexing: Proper indexes on PARTITION BY and ORDER BY columns significantly improve performance

2. Large Datasets: ROW_NUMBER() is generally fastest, while RANK() and DENSE_RANK() may have overhead due to tie handling

3. Memory Usage: Window functions can be memory-intensive on large partitions

Advanced Example: Top 2 Earners Per Department

sql

WITH RankedEmployees AS (
    SELECT 
        Department,
        Name,
        Salary,
        DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as RankValue
    FROM Employees
)
SELECT Department, Name, Salary, RankValue
FROM RankedEmployees
WHERE RankValue <= 2;

Result:

Department	Name	Salary	RankValue
IT	Alice	70000.00	1
IT	Charlie	65000.00	2
Sales	Jane	60000.00	1
Sales	Bob	60000.00	1	-- Both top earners in Sales

Best Practices

1. Use ROW_NUMBER() for unique sequencing and pagination

2. Use RANK() when you need to maintain ranking gaps (e.g., Olympic medals)

3. Use DENSE_RANK() for continuous ranking without gaps

4. Always specify ORDER BY - it's mandatory for ranking functions

5. Use PARTITION BY for grouping within window functions

6. Consider indexing on columns used in PARTITION BY and ORDER BY clauses

Common Pitfalls

1. Missing ORDER BY: Will cause syntax error

2. Forgetting PARTITION BY: May produce unexpected results across groups

3. Assuming order: Without explicit ORDER BY, the order is not guaranteed

4. Performance issues: Poorly designed window functions can be slow on large datasets

Conclusion

Understanding the differences between RANK, DENSE_RANK, and ROW_NUMBER is crucial for effective data analysis in SQL Server. ROW_NUMBER provides unique sequencing, RANK allows for ties with gaps, and DENSE_RANK provides continuous ranking without gaps. Choose the appropriate function based on your specific use case, considering both the desired output and performance implications. These window functions are powerful tools for complex sorting, ranking, and pagination requirements in modern database applications.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone