SQL Pagination

A deep-dive walkthrough of pagination in SQL Server — covering OFFSET/FETCH, why ORDER BY is mandatory and why it must be deterministic, calculating page numbers, getting the total row count without a second round trip, why deep pages get slower (and how keyset pagination fixes it), the indexing that makes pagination fast, wrapping it in a stored procedure, and how EF Core's Skip/Take maps onto all of this.

---

Table of Contents

1. Introduction
2. What Pagination Actually Is
3. OFFSET / FETCH: The Core Syntax
4. Why ORDER BY Is Mandatory (and Must Be Deterministic)
5. Calculating Page Numbers
6. Getting the Total Row Count
7. The Hidden Cost: Deep Pages Get Slower
8. Keyset (Seek) Pagination
9. Indexing for Pagination
10. Pagination Inside a Stored Procedure
11. Older Approaches: TOP and ROW_NUMBER()
12. Pagination in EF Core
13. Common Pitfalls
14. Quick Reference Table
15. Conclusion

---

Introduction

Pagination means retrieving data in smaller chunks instead of loading all records at once. Instead of asking the database for every row in a table and letting the application (or the user's browser) sort out what to show, you ask for exactly one "page" at a time — say, rows 21 through 30 — and fetch the next page only when it's needed. This is especially important when working with large datasets, where loading everything wastes memory, network bandwidth, and database resources on rows nobody will ever look at.

The standard SQL Server approach (SQL Server 2012 and later) is the OFFSET/FETCH clause:

SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
-- skip the first 20 rows, then return the next 10 — i.e. page 3 at a page size of 10

This guide goes beyond the syntax. It covers the mechanics that matter in production: why the ORDER BY has to be deterministic or pages will silently overlap, why OFFSET gets slower the deeper you page, the keyset alternative that stays fast at any depth, and the indexing that underpins both.

---

1. What Pagination Actually Is

Returning a bounded slice of an ordered result set

Without pagination:
  SELECT * FROM Orders;          -- 5,000,000 rows -> memory, network, UI all suffer

With pagination:
  Page 1 -> rows   1-10
  Page 2 -> rows  11-20
  Page 3 -> rows  21-30          -- only the slice that is actually displayed

Pagination solves three distinct problems at once:

• Database and network load — only the requested rows are read, shaped, and sent over the wire.
• Application memory — the client never has to materialize millions of rows.
• User experience — a screen that renders 10–50 rows is fast and usable; one that renders 5 million is neither.

Conceptually, pagination is always two things combined: a stable ordering of the full result set, and a window (skip N, take M) over that ordering. Everything in this guide is about getting one or both of those right.

---

2. OFFSET / FETCH: The Core Syntax

OFFSET skips rows; FETCH NEXT limits how many come back

SELECT Id, CustomerId, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS            -- how many rows to SKIP
FETCH NEXT 10 ROWS ONLY;  -- how many rows to RETURN after skipping

A few precise syntax rules worth knowing up front:

- OFFSET ... FETCH is part of the ORDER BY clause — it cannot exist without ORDER BY.
- FETCH requires OFFSET. (To start from the first row, use OFFSET 0 ROWS.)
- OFFSET is optional on its own: OFFSET 20 ROWS with no FETCH
  returns EVERYTHING after the first 20 rows.
- ROW and ROWS are interchangeable; FIRST and NEXT are interchangeable.
  OFFSET 1 ROW FETCH FIRST 1 ROW ONLY is valid.
- It cannot be combined with TOP in the same query.

Using variables instead of literals

DECLARE @Offset INT = 20;
DECLARE @PageSize INT = 10;

SELECT Id, CustomerId, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

Both values can be variables, parameters, or even expressions — which is what makes OFFSET/FETCH practical inside stored procedures and parameterized application queries (Section 9 and Section 11). The first page is simply OFFSET 0 ROWS.

---

3. Why ORDER BY Is Mandatory (and Must Be Deterministic)

A relational table has no inherent order — "page 2" is meaningless without one

SQL Server does NOT guarantee any row order unless you specify ORDER BY.
Without one, "skip 20, take 10" has no defined meaning — the engine would
be free to return ANY 10 rows, and a different 10 next time.

This is why OFFSET/FETCH is syntactically bound to ORDER BY — the language refuses to let you paginate over an undefined ordering at all.

But a non-unique ORDER BY is a subtler, silent bug

-- ❌ DANGEROUS — many orders can share the same OrderDate
SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

If multiple rows share the same OrderDate, SQL Server is free to return those tied rows in any order, and that order can legitimately differ between two executions of the same query — particularly once the plan changes, parallelism kicks in, or data shifts. The result: a row can appear on page 2 and again on page 3, while a different row is skipped entirely and never appears on any page. There's no error, no warning — just quietly wrong pages.

-- ✅ SAFE — add a unique tie-breaker (typically the primary key) as the LAST sort column
SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Rule: the ORDER BY for paginated queries must produce a TOTAL order.
  Whatever column(s) the user sorts by, ALWAYS append a unique column
  (usually the primary key) at the end so no two rows ever tie.

This is the single most common correctness bug in hand-written pagination, and it's worth treating as non-negotiable.

---

4. Calculating Page Numbers

Converting a 1-based page number into an offset

OFFSET = (PageNumber - 1) * PageSize
FETCH  = PageSize

Page size 10:
  Page 1 -> OFFSET  0
  Page 2 -> OFFSET 10
  Page 3 -> OFFSET 20      <-- the example at the top of this guide
  Page 4 -> OFFSET 30

DECLARE @PageNumber INT = 3;
DECLARE @PageSize   INT = 10;

SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Validate inputs — never trust the caller's page number

- PageNumber < 1  -> a NEGATIVE offset, which raises an error
- PageSize = 0 or huge -> an empty page, or defeats the whole purpose of paging

In real code, clamp the values (PageNumber at least 1; PageSize within a sensible range such as 1–100). An API that lets a caller request PageSize = 10,000,000 has quietly reintroduced the exact problem pagination exists to solve.

---

5. Getting the Total Row Count

Most UIs need "Page 3 of 47" — which requires knowing the total

-- Option A: a second query (simple, but runs the filter twice)
SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped';

-- Option B: COUNT(*) OVER() — total returned alongside every row in ONE query
SELECT
    Id, OrderDate, Total,
    COUNT(*) OVER() AS TotalRows      -- same value repeated on every row of the page
FROM Orders
WHERE Status = 'Shipped'
ORDER BY OrderDate DESC, Id DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

COUNT(*) OVER() is evaluated over the entire filtered set before the OFFSET/FETCH window is applied, so every returned row carries the full total. The application reads TotalRows from the first row and computes the page count:

TotalPages = CEILING(TotalRows / PageSize)

Worth knowing precisely:

- If the requested page is PAST the end, zero rows come back — so there is
  no row to read TotalRows from. Handle the empty-page case explicitly
  (e.g. fall back to a separate COUNT, or return TotalRows = 0).
- Computing the total is NOT free: it must visit every row matching the
  filter. On very large tables this count can cost more than the page itself.
  Many large systems avoid exact totals and show "Next / Previous" only,
  or cache an approximate count.

---

6. The Hidden Cost: Deep Pages Get Slower

OFFSET does not jump to row N — it reads and discards N rows

OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY
  -> SQL Server must still PRODUCE the first 1,000,000 rows in order,
     THROW THEM AWAY, and only then return the next 10.

This is the central performance fact about OFFSET pagination: the cost grows with the offset. Page 1 is nearly instant; page 100,000 does a million rows' worth of work to hand you ten. You can see it directly in the execution plan (per this series' Execution Plans guide): the operator feeding the Top has to read far more rows than the query ultimately returns.

Page 1        -> reads ~10 rows        -> fast
Page 1,000    -> reads ~10,000 rows    -> noticeably slower
Page 100,000  -> reads ~1,000,000 rows -> slow, and gets worse as data grows

For most business applications — where users rarely go beyond the first few pages — this is perfectly acceptable. It becomes a real problem for:

• very large tables,
• "infinite scroll" feeds,
• APIs or export jobs that walk through every page sequentially.

A second, separate weakness: because OFFSET counts rows from the start each time, inserts or deletes between page requests shift the window. A new row inserted at the top while the user is on page 2 pushes one row from page 2 onto page 3 — so the user sees a duplicate on the next page. Keyset pagination (next section) avoids both problems.

---

7. Keyset (Seek) Pagination

Instead of "skip N rows," say "give me rows AFTER the last one I saw"

-- Page 1: no anchor yet
SELECT TOP (10) Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC;

-- The application remembers the LAST row's values from this page:
--   @LastOrderDate = '2026-03-14 09:30', @LastId = 48213

-- Next page: seek directly to the position after the last row seen
DECLARE @LastOrderDate DATETIME2 = '2026-03-14 09:30';
DECLARE @LastId        INT       = 48213;

SELECT TOP (10) Id, OrderDate, Total
FROM Orders
WHERE OrderDate < @LastOrderDate
   OR (OrderDate = @LastOrderDate AND Id < @LastId)   -- tie-breaker on equal dates
ORDER BY OrderDate DESC, Id DESC;

Instead of counting and discarding rows, the WHERE clause lets SQL Server seek straight to the right position in an index and read only the 10 rows it needs — so page 1 and page 100,000 cost essentially the same. It's also stable under inserts and deletes, because the anchor is a value, not a row count.

The trade-offs, stated honestly

Advantages:
  - Constant cost per page, regardless of depth
  - Stable results even as data is inserted/deleted between requests

Limitations:
  - No "jump to page 57" — you can only go Next/Previous from a known position
  - Needs a unique, indexed ordering (hence the Id tie-breaker)
  - The WHERE clause gets more complex with each additional sort column
  - Going BACKWARD requires reversing the comparison and sort direction,
    then re-reversing the results
  - The ORDER BY direction and the comparison operators must match exactly

Choosing between them:
  Numbered pages + jump-to-page + modest data  ->  OFFSET / FETCH
  Infinite scroll, feeds, APIs, huge tables,
  or sequential export of everything           ->  Keyset

Note that T-SQL does not support row-value comparison like (OrderDate, Id) < (@d, @id), which is why the expanded OR (... AND ...) form above is required.

---

8. Indexing for Pagination

The ORDER BY is the expensive part — an index that matches it removes the sort

CREATE INDEX IX_Orders_OrderDate_Id
ON Orders (OrderDate DESC, Id DESC);

Without a supporting index, SQL Server must read the matching rows and sort the entire set just to find the first 10 — and that sort cost is paid on every single page request. With an index whose key order matches the ORDER BY, the rows are already in the right order, so the engine can start at the right place and stop after the page is full (per this series' Indexes and Execution Plans guides).

Guidelines:
  - Match the index key columns and directions to the ORDER BY
    (including the unique tie-breaker column).
  - If the query has a WHERE filter, put the equality-filter column(s)
    FIRST in the index, then the ORDER BY columns:
        WHERE Status = 'Shipped' ORDER BY OrderDate DESC, Id DESC
        -> INDEX (Status, OrderDate DESC, Id DESC)
  - Add INCLUDE columns for the selected columns to avoid key lookups
    on every row of the page.
  - Verify in the execution plan: look for an Index Seek/Scan with NO
    separate Sort operator.

Every extra index speeds reads and slows writes — add the ones that back
  your genuinely hot, paginated screens, not one per possible sort column.

---

9. Pagination Inside a Stored Procedure

A reusable, parameterized, validated paging query

CREATE PROCEDURE GetOrdersPage
    @PageNumber INT = 1,
    @PageSize   INT = 10,
    @Status     NVARCHAR(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Clamp inputs: never trust the caller
    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @PageSize   < 1 SET @PageSize   = 10;
    IF @PageSize   > 100 SET @PageSize = 100;

    SELECT
        Id, CustomerId, OrderDate, Status, Total,
        COUNT(*) OVER() AS TotalRows
    FROM Orders
    WHERE (@Status IS NULL OR Status = @Status)
    ORDER BY OrderDate DESC, Id DESC          -- deterministic (Section 3)
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

EXEC GetOrdersPage @PageNumber = 3, @PageSize = 10, @Status = 'Shipped';

A word of caution that connects directly to this series' Stored Procedures guide: the optional-filter pattern (@Status IS NULL OR Status = @Status) is a classic parameter-sniffing trap — the plan cached for the first call (say, with @Status = NULL) gets reused for very different calls. If you see input-dependent slowness here, adding OPTION (RECOMPILE) to this single statement is the surgical fix, as that guide describes.

---

10. Older Approaches: TOP and ROW_NUMBER()

What you'll meet in legacy code (pre-SQL Server 2012)

-- ROW_NUMBER() approach: number every row, then filter on the number
WITH Numbered AS (
    SELECT
        Id, OrderDate, Total,
        ROW_NUMBER() OVER (ORDER BY OrderDate DESC, Id DESC) AS RowNum
    FROM Orders
)
SELECT Id, OrderDate, Total
FROM Numbered
WHERE RowNum BETWEEN 21 AND 30      -- page 3 at page size 10
ORDER BY RowNum;

TOP approach (nested TOP / reversed ORDER BY): even clumsier, and error-prone.

On modern SQL Server, OFFSET/FETCH is clearer, shorter, and the standard choice. The ROW_NUMBER() form is still useful to recognize in older codebases — and it has the same core cost characteristic: rows before the window still have to be numbered.

---

11. Pagination in EF Core

Skip and Take translate directly to OFFSET / FETCH

int pageNumber = 3;
int pageSize   = 10;

var orders = await context.Orders
    .OrderByDescending(o => o.OrderDate)
    .ThenByDescending(o => o.Id)               // the deterministic tie-breaker (Section 3)
    .Skip((pageNumber - 1) * pageSize)         // -> OFFSET 20 ROWS
    .Take(pageSize)                            // -> FETCH NEXT 10 ROWS ONLY
    .AsNoTracking()                            // read-only list: skip change-tracking overhead
    .ToListAsync();

This is the same mechanism this series' EF Core guide describes: LINQ is translated into SQL, and Skip/Take become OFFSET/FETCH. Two points deserve emphasis:

- Always put OrderBy BEFORE Skip/Take, and include the unique tie-breaker
  via ThenBy. EF Core will warn if you use Skip/Take without any ordering,
  because the page contents would be undefined.
- Skip/Take must be applied on the IQueryable, BEFORE ToListAsync() — 
  otherwise you've already loaded every row into memory and are
  "paginating" an in-memory list (this series' IEnumerable vs. IQueryable guide).

Total count alongside the page

var query = context.Orders.Where(o => o.Status == "Shipped");

int totalRows = await query.CountAsync();      // one COUNT query

var page = await query
    .OrderByDescending(o => o.OrderDate)
    .ThenByDescending(o => o.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync();                            // a second query for the page itself

Keyset pagination in LINQ

var nextPage = await context.Orders
    .Where(o => o.OrderDate < lastOrderDate
             || (o.OrderDate == lastOrderDate && o.Id < lastId))
    .OrderByDescending(o => o.OrderDate)
    .ThenByDescending(o => o.Id)
    .Take(pageSize)                            // no Skip at all — the WHERE does the seeking
    .AsNoTracking()
    .ToListAsync();

This produces exactly the WHERE ... ORDER BY ... TOP shape from Section 7, with the same constant-cost-per-page behavior.

---

12. Common Pitfalls

Pitfall	Why it hurts	Better approach
Paginating without an ORDER BY	Row order is undefined, so "page 2" has no stable meaning (and OFFSET/FETCH won't even compile without it)	Always specify an explicit ORDER BY (Section 3)
Non-unique ORDER BY (e.g. just OrderDate)	Tied rows can swap places between executions — rows duplicate across pages or vanish entirely, silently	Append a unique tie-breaker such as the primary key (Section 3)
Using OFFSET for very deep pages on huge tables	SQL Server reads and discards every skipped row — cost grows with the offset	Switch to keyset pagination for deep or sequential paging (Section 7)
No index matching the ORDER BY	The whole filtered set is sorted on every page request	Create an index matching the sort columns and directions (Section 8)
Running COUNT(*) on every page request over a huge table	The count must scan every matching row and can cost more than the page itself	Cache or approximate the total, or drop exact totals in favor of Next/Previous (Section 5)
Not validating page number and page size	A negative offset raises an error; a giant page size defeats the point of paging	Clamp @PageNumber ≥ 1 and @PageSize to a sensible maximum (Section 4, 9)
Calling ToList() before Skip/Take in EF Core	Loads the entire table into memory, then "paginates" in C#	Apply OrderBy/Skip/Take on the IQueryable, then materialize (Section 11)
Forgetting the empty-last-page case when using COUNT(*) OVER()	A page past the end returns zero rows, so there's no row to read the total from	Handle zero-row results explicitly (Section 5)
Optional-filter pattern (@P IS NULL OR Col = @P) in a paged procedure	A parameter-sniffing trap: one cached plan reused for very different parameter values	Apply OPTION (RECOMPILE) to that statement if input-dependent slowness appears (Section 9)

---

Quick Reference Table

Concept	Syntax	Purpose
Basic paging	ORDER BY ... OFFSET n ROWS FETCH NEXT m ROWS ONLY	Skip n rows, return the next m
First page	OFFSET 0 ROWS FETCH NEXT m ROWS ONLY	Start from the first row
Page → offset	OFFSET (@Page - 1) * @Size ROWS	Convert a 1-based page number to a row offset
Total in one query	COUNT(*) OVER() AS TotalRows	Total filtered rows returned with every row
Total pages	CEILING(TotalRows / PageSize)	Number of pages (cast to decimal before dividing)
Deterministic order	ORDER BY SortCol, Id	Unique tie-breaker so pages never overlap or skip
Keyset paging	WHERE Col < @Last OR (Col = @Last AND Id < @LastId) + TOP (m)	Constant-cost "rows after the last seen"
Supporting index	CREATE INDEX ... (SortCol DESC, Id DESC)	Removes the sort; enables efficient seeks
EF Core offset paging	.OrderBy(...).ThenBy(...).Skip(n).Take(m)	Translates to OFFSET/FETCH
EF Core keyset paging	.Where(...).OrderBy(...).Take(m)	Translates to WHERE ... TOP

---

Conclusion

Pagination is conceptually simple — a stable ordering plus a skip-and-take window — and OFFSET/FETCH makes the syntax nearly trivial. But the details that decide whether it works correctly and at scale are exactly the ones that are easy to miss: a non-deterministic ORDER BY that silently duplicates and drops rows across pages, an OFFSET whose cost climbs with every page deeper you go, a total count that quietly costs more than the data it's counting, and a missing index that forces a full sort on every request.

The practical takeaway is a decision, not a rule: use OFFSET/FETCH with a deterministic ORDER BY and a matching index for ordinary, numbered-page screens, where simplicity and "jump to page N" matter and datasets are moderate; reach for keyset pagination when you're dealing with huge tables, infinite scroll, or APIs that walk every page in sequence. Understanding why each behaves the way it does — rather than memorizing the syntax — is what lets you pick the right one deliberately, exactly the way this whole series treats every other trade-off it covers.

---

Found this useful? Feel free to star the repo, open an issue with corrections, or share the "page 2 and page 3 showed the same order twice" bug story that made the case for a deterministic ORDER BY click better than any abstract explanation.