SQL Server Window Functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE (2026)

1. What Are Window Functions?

A window function performs a calculation across a set of rows that are somehow related to the current row — its "window" — without collapsing those rows into a single output row. That is the key distinction from GROUP BY: both aggregate, but GROUP BY reduces many rows to one, while a window function adds a computed column alongside every original row.

Anatomy of the OVER() Clause

Every window function is paired with an OVER() clause that defines the window:

function_name()  OVER (
    [ PARTITION BY partition_expression [, ...] ]
    [ ORDER BY     sort_expression      [, ...] ]
    [ ROWS | RANGE BETWEEN ... AND ...  ]
)

• PARTITION BY — divides rows into independent groups (like GROUP BY for the window). The function resets for each partition.
• ORDER BY — defines the sort order within each partition. Required for ranking functions.
• ROWS/RANGE frame — narrows the window further (used by aggregate window functions; not needed for ranking functions).

The Four Ranking Functions

SQL Server provides four built-in ranking window functions:

2. ROW_NUMBER(): Unique Sequential Numbers

ROW_NUMBER() assigns a unique integer to every row within a partition, starting at 1 and incrementing by 1. No two rows within the same partition share a number — even if the ORDER BY column has duplicate values, SQL Server assigns them arbitrarily but distinctly.

Basic Syntax

SELECT
    EmployeeID,
    DepartmentID,
    Salary,
    ROW_NUMBER() OVER (
        PARTITION BY DepartmentID
        ORDER BY Salary DESC
    ) AS SalaryRank
FROM HR.Employees;

Result: within each department, employees are numbered 1, 2, 3 … in descending salary order. A new counter starts for every new DepartmentID.

3. ROW_NUMBER() Use Case 1 — Efficient Pagination

Before OFFSET…FETCH was introduced in SQL Server 2012, ROW_NUMBER() was the standard pagination pattern. It is still useful for keyset pagination on very large tables because it avoids the page-scan cost of OFFSET.

Classic OFFSET/FETCH (SQL Server 2012+)

-- Page 3, 20 rows per page
SELECT ProductID, ProductName, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC
OFFSET 40 ROWS          -- (page - 1) * pageSize
FETCH NEXT 20 ROWS ONLY;

ROW_NUMBER() Keyset Pagination (faster at depth)

-- Returns rows 41–60
WITH Paged AS (
    SELECT
        ProductID,
        ProductName,
        ListPrice,
        ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS rn
    FROM Production.Product
)
SELECT ProductID, ProductName, ListPrice
FROM   Paged
WHERE  rn BETWEEN 41 AND 60
ORDER BY rn;

4. ROW_NUMBER() Use Case 2 — Deduplication

A classic ETL challenge: a source table contains duplicate rows and you need to keep only the most recent one per business key. ROW_NUMBER() makes this a one-pass operation.

-- Step 1: identify duplicates
WITH Ranked AS (
    SELECT
        CustomerID,
        Email,
        UpdatedAt,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerID          -- group by business key
            ORDER BY UpdatedAt DESC          -- keep newest
        ) AS rn
    FROM Staging.Customers
)
-- Step 2: delete all but the latest
DELETE FROM Ranked
WHERE rn > 1;

To select the deduplicated set (instead of deleting) simply change the final step:

SELECT CustomerID, Email, UpdatedAt
FROM   Ranked
WHERE  rn = 1;

5. ROW_NUMBER() Use Case 3 — Top-N Per Group

Fetching the top 3 best-selling products per category is impossible with a simple TOP (3) — that returns only 3 rows globally. ROW_NUMBER() with PARTITION BY solves this elegantly.

WITH RankedProducts AS (
    SELECT
        p.CategoryID,
        p.ProductName,
        SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
        ROW_NUMBER() OVER (
            PARTITION BY p.CategoryID
            ORDER BY SUM(od.Quantity * od.UnitPrice) DESC
        ) AS rn
    FROM Sales.OrderDetails od
    JOIN Production.Products  p  ON p.ProductID = od.ProductID
    GROUP BY p.CategoryID, p.ProductName
)
SELECT CategoryID, ProductName, TotalRevenue
FROM   RankedProducts
WHERE  rn <= 3
ORDER  BY CategoryID, rn;

This returns exactly the top 3 products for every category in a single scan — no correlated subquery, no self-join.

6. RANK(): Tied Rankings Leave Gaps

RANK() behaves like Olympic medal rankings: two athletes who both run 9.8 s both get rank 1, and the next athlete gets rank 3 (rank 2 is skipped). This gap behaviour can be intentional — for example, when the rank number itself carries meaning ("there are two better-paid employees above me").

SELECT
    EmployeeID,
    FirstName,
    LastName,
    DepartmentID,
    Salary,
    RANK() OVER (
        PARTITION BY DepartmentID
        ORDER BY Salary DESC
    ) AS SalaryRank
FROM HR.Employees
ORDER BY DepartmentID, SalaryRank;

Sample output for one department:

Rank 2 is absent because two employees share rank 1. The count of employees with a better salary than you plus one equals your rank.

7. DENSE_RANK(): No Gaps in Tied Rankings

DENSE_RANK() also gives tied rows the same rank, but the next distinct value always receives the next consecutive integer — no gap. Use it when the rank number should represent a position in a meaningful sequence (e.g., "how many distinct salary bands are above me?").

SELECT
    EmployeeID,
    Salary,
    RANK()       OVER (ORDER BY Salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS dense_rnk
FROM HR.Employees
ORDER BY Salary DESC;

Side-by-side comparison:

When to Use RANK vs DENSE_RANK

• Use RANK() when the numeric rank value should reflect the true position (e.g., "ranked 3rd because two people beat you"). Common in sports leaderboards.
• Use DENSE_RANK() when rank indicates a tier or band (e.g., "salary band 2 of 5"). Common in HR banding, pricing tiers, cohort analysis.
• Use ROW_NUMBER() when you need guaranteed uniqueness — pagination, deduplication, top-N.

8. NTILE(n): Dividing Rows into Buckets

NTILE(n) divides the ordered partition into n roughly equal buckets and assigns each row a bucket number from 1 to n. It is the standard T-SQL tool for quartile, quintile, decile, or percentile analysis.

Quartile Analysis

-- Segment customers into four revenue quartiles
SELECT
    CustomerID,
    TotalRevenue,
    NTILE(4) OVER (ORDER BY TotalRevenue DESC) AS RevenueQuartile
FROM (
    SELECT CustomerID, SUM(OrderAmount) AS TotalRevenue
    FROM Sales.Orders
    GROUP BY CustomerID
) AS CustomerRevenue
ORDER BY TotalRevenue DESC;

Bucket 1 = top 25 % spenders, bucket 4 = bottom 25 %.

Percentile Buckets (Deciles)

SELECT
    ProductID,
    ListPrice,
    NTILE(10) OVER (ORDER BY ListPrice) AS PriceDecile
FROM Production.Product
WHERE ListPrice > 0
ORDER BY ListPrice;

Partitioned NTILE

-- Quartile within each product category
SELECT
    CategoryID,
    ProductID,
    ListPrice,
    NTILE(4) OVER (
        PARTITION BY CategoryID
        ORDER BY ListPrice DESC
    ) AS CategoryQuartile
FROM Production.Product
WHERE ListPrice > 0;

9. Combining Ranking Functions in One Query

You can include multiple window functions in the same SELECT, each with its own OVER() definition. SQL Server evaluates them in one pass over the data.

-- Comprehensive employee ranking report
SELECT
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName   AS EmployeeName,
    d.DepartmentName,
    e.Salary,

    -- Unique row number per dept (for pagination/dedup)
    ROW_NUMBER()  OVER (PARTITION BY e.DepartmentID ORDER BY e.Salary DESC)
        AS RowNum,

    -- Olympic-style rank with gaps
    RANK()        OVER (PARTITION BY e.DepartmentID ORDER BY e.Salary DESC)
        AS SalaryRank,

    -- Tier rank, no gaps
    DENSE_RANK()  OVER (PARTITION BY e.DepartmentID ORDER BY e.Salary DESC)
        AS SalaryTier,

    -- Salary quartile company-wide (no PARTITION BY)
    NTILE(4)      OVER (ORDER BY e.Salary DESC)
        AS CompanyQuartile

FROM HR.Employees e
JOIN HR.Departments d ON d.DepartmentID = e.DepartmentID
ORDER BY d.DepartmentName, SalaryRank;

This single query delivers four different perspectives on the same data without any self-joins or subqueries in the FROM clause.

10. OVER() with No PARTITION BY

Omitting PARTITION BY treats the entire result set as one partition. The function operates globally across all rows — useful for company-wide rankings, overall sequence numbers, or global bucket assignments.

-- Rank all products by price across the entire catalog
SELECT
    ProductID,
    ProductName,
    ListPrice,
    RANK() OVER (ORDER BY ListPrice DESC) AS GlobalPriceRank
FROM Production.Product
WHERE Discontinued = 0
ORDER BY GlobalPriceRank;

-- Assign a global sequential order to all orders (for audit trails)
SELECT
    OrderID,
    OrderDate,
    CustomerID,
    ROW_NUMBER() OVER (ORDER BY OrderDate, OrderID) AS GlobalOrderSeq
FROM Sales.Orders;

11. Ordering Ties: How SQL Server Breaks Them

When the ORDER BY inside OVER() has duplicate values, SQL Server does not guarantee a stable row order for ROW_NUMBER() — it will assign distinct numbers, but which row gets 1 and which gets 2 is non-deterministic run to run.

Add a Tiebreaker Column

-- Add a unique tiebreaker to make the order deterministic
ROW_NUMBER() OVER (
    PARTITION BY DepartmentID
    ORDER BY Salary DESC, EmployeeID   -- EmployeeID breaks ties deterministically
) AS StableRowNum

Best practice: always include a unique column (primary key or identity) as the last item in the ORDER BY of any ranking function where determinism matters.

12. Performance: Index Design for Window Functions

Window functions are evaluated in the SELECT phase, after WHERE and JOIN. SQL Server usually introduces a Segment and Sequence Project operator in the execution plan. To make these operators fast, the data must already arrive sorted in the order demanded by the OVER() clause.

The Ideal Covering Index

For ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC):

-- Covering index: PARTITION BY columns first, then ORDER BY columns
CREATE INDEX IX_Employees_DeptSalary
ON HR.Employees (DepartmentID, Salary DESC)
INCLUDE (EmployeeID, FirstName, LastName);

• Leading columns: all PARTITION BY columns.
• Next columns: all ORDER BY columns with matching sort direction.
• INCLUDE: any additional SELECT columns so the query is a covering scan (no key lookup).

Partitioning and Parallelism

Window functions with PARTITION BY can execute in parallel — SQL Server can assign each partition to a separate thread. Without PARTITION BY (global ranking), parallelism is often blocked because the entire dataset must be ordered globally. Keep this in mind for very large tables.

13. Common Mistakes and How to Avoid Them

Mistake 1: Using ROW_NUMBER() Directly in WHERE

-- ❌ WRONG — window functions are not allowed in WHERE
SELECT EmployeeID, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
FROM HR.Employees
WHERE ROW_NUMBER() OVER (ORDER BY Salary DESC) = 1;  -- ERROR

-- ✅ CORRECT — wrap in a CTE or subquery first
WITH Ranked AS (
    SELECT EmployeeID, Salary,
           ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
    FROM HR.Employees
)
SELECT EmployeeID, Salary FROM Ranked WHERE rn = 1;

Mistake 2: ORDER BY Without PARTITION BY When You Mean Per-Group

-- ❌ This ranks globally, not per department
ROW_NUMBER() OVER (ORDER BY Salary DESC)

-- ✅ This ranks within each department
ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC)

Mistake 3: Confusing RANK and DENSE_RANK in Top-N Queries

-- If two employees both earn 95,000 and you want the top 2 earners:

-- RANK: rn <= 2 returns BOTH employees (both have rank 1) ✅ (good for ties)
-- DENSE_RANK: drnk <= 2 returns employees in bands 1 and 2 — may return 3+ rows ⚠️
-- ROW_NUMBER: rn <= 2 returns exactly 2 rows, but which tied row is excluded is arbitrary ⚠️

Choose the function that matches the business requirement for ties.

Mistake 4: Missing ORDER BY Inside OVER() for Ranking Functions

-- ❌ RANK requires ORDER BY — this is a syntax error
RANK() OVER (PARTITION BY DepartmentID)

-- ✅ Always include ORDER BY for ranking functions
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC)

Mistake 5: Expecting NTILE to Produce Exact Equal Buckets

With 10 rows and NTILE(3), bucket 1 gets 4 rows, buckets 2 and 3 get 3 each — not 3.33 each. Account for this in downstream aggregations when bucket size parity matters.