SQL Server pagination that stays fast on deep pages

A customer opens an order history screen. The first page appears immediately, but page 8,000 takes several seconds. The application still returns only 25 rows, so increasing the page size or adding application servers seems unlikely to help. The expensive part is often locating the starting position: OFFSET asks SQL Server to pass over earlier rows before returning the requested slice.

For an ordered index, this does not necessarily require sorting the entire table, but the skipped entries still represent work. A useful investigation compares logical reads for an early page and a late page with the same filters. If those reads grow with the offset, the pagination contract deserves attention. This is different from a query that is slow even on page one because it sorts an unindexed expression.

Define a boundary, not just a page number

Keyset pagination carries the last ordering values from the preceding response. For an order history sorted newest first, the next request asks for rows older than the last timestamp, plus smaller IDs when timestamps match. The second condition is essential. A timestamp alone is rarely unique, and excluding every row with the same timestamp can silently omit orders.

The following example runs entirely in a temporary table. The first query returns 105 and 104. The second must return 103 and 102. Its purpose is to make the boundary rules inspectable before applying them to a large production table.

CREATE TABLE #Orders
(
    OrderId bigint NOT NULL PRIMARY KEY,
    CreatedAt datetime2(3) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #Orders VALUES
(105,'2022-01-10T09:00:00',25),
(104,'2022-01-10T09:00:00',40),
(103,'2022-01-09T12:00:00',15),
(102,'2022-01-08T08:00:00',80),
(101,'2022-01-07T08:00:00',30);

CREATE INDEX IX_Orders_Page
ON #Orders(CreatedAt DESC, OrderId DESC)
INCLUDE(Amount);

SELECT TOP (2) OrderId, CreatedAt, Amount
FROM #Orders
ORDER BY CreatedAt DESC, OrderId DESC;

DECLARE @LastTime datetime2(3) = '2022-01-10T09:00:00';
DECLARE @LastId bigint = 104;

SELECT TOP (2) OrderId, CreatedAt, Amount
FROM #Orders
WHERE CreatedAt < @LastTime
   OR (CreatedAt = @LastTime AND OrderId < @LastId)
ORDER BY CreatedAt DESC, OrderId DESC;

DROP TABLE #Orders;

The index puts the filtering and ordering columns where the engine can navigate them and includes the amount needed for the response. In a multitenant application, a common starting point is an index on TenantId, CreatedAt, OrderId, with TenantId constrained to one value. An index for all tenants ordered by time is a different access pattern. Do not assume one design serves both equally well.

Make the API contract explicit

Return the exact timestamp precision and ID in an opaque cursor. Rounding datetime2 values through a client date object can move the boundary. Include or bind the tenant, filters, ordering direction, and cursor format version. A cursor generated for unpaid orders should not be silently reused for all orders. Signing a cursor can detect tampering, but authorization must still come from the authenticated request.

For the first request, use a separate query without a boundary. Adding an optional parameter branch such as "cursor is null OR ..." can produce a less selective reusable plan. Compare the actual plans for both forms. The OR in the lexicographic predicate can also appear partly as a residual filter; verify rows read and logical reads rather than promising that every keyset query becomes a perfect seek.

Fetch one extra row to determine whether another page exists, then return only the requested count. Construct the next cursor from the last row actually returned. Using the extra row with a strict less-than predicate would skip it. A separate exact COUNT over the entire result can cost more than fetching the page; most activity feeds do not need that count on every request.

Decide what concurrent changes mean

New rows arriving above the boundary do not normally shift the next page, which is valuable for browsing. Changes to ordering values can still move rows across the boundary and cause duplicates or omissions. Prefer immutable ordering columns. For an audit export requiring a frozen dataset, ordinary independent reads are insufficient: consider a snapshot transaction of controlled duration, a materialized export set, or an explicit immutable export boundary.

Test identical timestamps, deleted boundary rows, an empty final page, and a tenant with very different data volume. A deleted boundary row is harmless when the cursor contains its former values; the query need not find that row again. For reverse navigation, invert the comparison and ordering, take a bounded result, then restore display order.

This design trades arbitrary page-number jumps for predictable sequential navigation. An administrative report may still need offset pagination or precomputed bookmarks. Choose from the actual interaction, then demonstrate that deep-page reads remain bounded on representative data. The success criterion is complete, correctly ordered results with stable resource use, not merely a faster first screenshot.

Technical references: Microsoft Learn: ORDER BY · Microsoft Learn: Pagination.