Keyset pagination in SQL Server: when OFFSET gets slow

The usual approach

Most list endpoints start with OFFSET ... FETCH:

SELECT TransactionId, PolicyNumber, Amount, CreatedAt
FROM dbo.Transactions
ORDER BY CreatedAt DESC, TransactionId DESC
OFFSET 20000 ROWS FETCH NEXT 50 ROWS ONLY;

It is easy to understand and maps directly to "page 401 of 50 rows". The problem is what SQL Server has to do to get there: it reads the first 20,050 rows in order and throws away 20,000 of them. Page 1 is fast; page 401 reads hundreds of times more data. On large, busy tables, deep pages and exports that loop through every page become the slowest queries in the system.

There is a second, quieter problem: if new rows are inserted while a user is paging, rows shift between pages. The user sees duplicates or misses records.

Keyset pagination

Instead of saying "skip N rows", say "continue after the last row I saw". The client sends back the sort key of the last row on the current page:

DECLARE @AfterCreatedAt DATETIME2 = '2026-10-01T08:15:00';
DECLARE @AfterId BIGINT = 918273;

SELECT TOP (50) TransactionId, PolicyNumber, Amount, CreatedAt
FROM dbo.Transactions
WHERE CreatedAt < @AfterCreatedAt
   OR (CreatedAt = @AfterCreatedAt AND TransactionId < @AfterId)
ORDER BY CreatedAt DESC, TransactionId DESC;

Two details make this correct:

• A unique tiebreaker. CreatedAt alone is not unique; two rows can share a timestamp. Adding TransactionId to both the ORDER BY and the WHERE makes the position unambiguous.
• The same direction everywhere. Descending sort means "less than" in the filter.

The index that makes it fast

Keyset pagination is only fast when an index matches the sort order, so SQL Server can seek directly to the starting point and read 50 rows:

CREATE INDEX IX_Transactions_CreatedAt_Id
    ON dbo.Transactions (CreatedAt DESC, TransactionId DESC)
    INCLUDE (PolicyNumber, Amount);

The INCLUDE columns cover the query, so it never needs to look up the base table. Compare the two queries with SET STATISTICS IO ON: the offset version's logical reads grow with the page number, the keyset version stays roughly flat no matter how deep you go.

If the list has a filter that is always present, such as a branch or an agent, put that column first in the index: (BranchId, CreatedAt DESC, TransactionId DESC).

The API shape

Expose an opaque cursor instead of a page number. Encoding the two values in Base64 keeps clients from depending on the internals:

{
  "items": [ "..." ],
  "nextCursor": "MjAyNi0xMC0wMVQwODoxNTowMHw5MTgyNzM="
}

The first request has no cursor. Each following request sends ?cursor=... from the previous response. When nextCursor is null, there are no more rows. A simple trick to know that: ask the database for TOP (51), return 50, and only create a cursor if the 51st row exists.

Trade-offs

Keyset pagination is not free:

• No jumping to page 37. Users can go next (and previous, with the reverse query), but not to an arbitrary page. For infinite scroll, mobile lists, exports, and sync jobs, that is fine. For a back-office grid where users really jump between pages, OFFSET on a filtered, indexed query is often good enough.
• Total counts are separate. COUNT(*) on a large table is its own cost. Show "1,000+" or compute it asynchronously if the UI does not strictly need it.
• Sort options multiply indexes. Every sort order you offer needs its own matching index to stay fast. Offer few, deliberate sort options.

Summary

• OFFSET reads and discards every skipped row, so deep pages get slower.
• Keyset pagination continues after the last seen key: constant cost per page, stable results.
• Always add a unique tiebreaker and an index in the same order.
• Return an opaque cursor; use offset paging only where jumping to a page number is truly needed.