Generate Sequence Numbers in SQL Server Queries

"Number these rows" sounds trivial and hides four different questions: number the result set, rank with ties, number within groups, or hand out persistent numbers that survive across inserts. SQL Server has a distinct tool for each — ROW_NUMBER, RANK/DENSE_RANK, PARTITION BY, and SEQUENCE/IDENTITY. This tutorial runs all of them on real data and shows exactly how their outputs differ.

ROW_NUMBER, RANK, DENSE_RANK — side by side

SELECT Name, Department, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
       RANK()       OVER (ORDER BY Salary DESC) AS Rnk,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRnk
FROM Employees
WHERE Department IN ('Sales', 'Engineering');

Name    Department   Salary     RowNum  Rnk  DenseRnk
------  -----------  ---------  ------  ---  --------
Amit    Engineering  140000.00  1       1    1
Neha    Sales        120000.00  2       2    2
Divya   Engineering   98000.00  3       3    3
Vikram  Engineering   95000.00  4       4    4
Tara    Sales         82000.00  5       5    5
Rohan   Sales         76000.00  6       6    6

With no ties the three look identical. The difference appears the moment two salaries match — say two people at 98000:

• ROW_NUMBER — always unique: … 3, 4 … (ties broken arbitrarily unless your ORDER BY disambiguates).
• RANK — ties share a number and the next rank skips: 3, 3, 5.
• DENSE_RANK — ties share and nothing skips: 3, 3, 4.

Pick by question: "give every row a unique number" → ROW_NUMBER; "competition placing (Olympic style)" → RANK; "how many distinct levels above me" → DENSE_RANK.

NTILE(n) completes the family — it deals rows into n buckets:

SELECT Name, Salary, NTILE(3) OVER (ORDER BY Salary DESC) AS Band
FROM Employees;

Name    Salary     Band
------  ---------  ----
Priya   250000.00  1
Amit    140000.00  1
Neha    120000.00  1
Divya    98000.00  2
Vikram   95000.00  2
Tara     82000.00  3
Rohan    76000.00  3

Salary bands, price tiers, A/B/C customer segmentation — one function.

PARTITION BY — numbering restarts per group

Add PARTITION BY and the numbering resets for each group — the engine behind every "top N per group" query:

WITH Ranked AS (
    SELECT Name, Department, Salary,
           ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
    FROM Employees
)
SELECT Department, Name, Salary
FROM Ranked
WHERE rn = 1
ORDER BY Department;

Department   Name   Salary
-----------  -----  ---------
Engineering  Amit   140000.00
Leadership   Priya  250000.00
Sales        Neha   120000.00

Highest earner per department. The CTE wrapper exists because window functions can't appear in WHERE directly. The same shape deduplicates (PARTITION BY Sku … WHERE rn > 1 deleted), pages (WHERE rn BETWEEN 21 AND 40 — though OFFSET/FETCH is cleaner for plain paging), and picks latest-row-per-entity (PARTITION BY CustomerId ORDER BY OrderDate DESC).

Numbering with no natural order

ROW_NUMBER demands an ORDER BY. When you genuinely don't care:

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn

Fine for assigning arbitrary ids to a staging load. Don't use it where order matters — "arbitrary" means the order can differ run to run.

Persistent numbers: IDENTITY and SEQUENCE

Everything above numbers a result set — recomputed per query. For numbers that stick to rows:

IDENTITY — per-table auto-numbering, assigned on insert:

CREATE TABLE Orders (OrderId INT IDENTITY(1,1) PRIMARY KEY, ...);

Simple and right for surrogate keys. Quirks worth knowing: failed inserts and rollbacks consume values (gaps are normal), and you read the assigned value with SCOPE_IDENTITY() — not @@IDENTITY, which can return a trigger's insert.

SEQUENCE — a standalone number generator, shareable across tables:

CREATE SEQUENCE dbo.InvoiceNumber START WITH 1000 INCREMENT BY 1;

SELECT NEXT VALUE FOR dbo.InvoiceNumber AS Invoice1,
       NEXT VALUE FOR dbo.InvoiceNumber AS Invoice2;

Invoice1  Invoice2
--------  --------
1000      1000

Both columns show 1000 — real output, and a documented rule: within a single row, all NEXT VALUE FOR references to the same sequence return the same value. Across rows it increments normally:

INSERT INTO Invoices (InvoiceNo, Total)
SELECT NEXT VALUE FOR dbo.InvoiceNumber, Amount FROM Orders;   -- 1001, 1002, 1003…

Use SEQUENCE over IDENTITY when several tables share one numbering scheme, when you need the number before inserting, or when you want CYCLE/MINVALUE control. Like IDENTITY, sequences don't promise gaplessness — for legally gapless invoice numbers, a serialized counter table updated in the insert's transaction is the (deliberately slower) tool.

Choosing