LLI Wiki — My Digital Knowledge Base

Paged list queries: OFFSET/FETCH vs keyset pagination

Rewriting a slow list query on Azure SQL Database: keyset pagination, supporting index, measuring with SET STATISTICS IO and Query Store.

A "list" screen is often the most executed query of an application, and it is rarely written with care: a SELECT with a few joins, an ORDER BY, and a page number. It works with 10,000 rows and hurts with 10 million. This article shows how I rewrite such queries on Azure SQL Database: first measure, then choose between OFFSET/FETCH and keyset (seek) pagination, then give the query the index it needs.

The problem with OFFSET/FETCH

SELECT o.OrderId, o.OrderDate, o.Total, c.Name
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Open'
ORDER BY o.OrderDate DESC, o.OrderId DESC
OFFSET 100000 ROWS FETCH NEXT 50 ROWS ONLY;

OFFSET does not jump: the engine produces and discards the first 100,000 rows, then returns 50. The cost grows linearly with the page number, so page 1 is fast and page 2,000 is slow. Those rows are also read (and locked or versioned) for nothing. Another side effect: if rows are inserted between two requests, the user sees duplicates or misses rows.

Keyset pagination

Instead of "skip N rows", the client sends the sort key of the last row it received, and the query seeks directly after it:

DECLARE @LastDate datetime2 = '2026-03-01T10:15:00', @LastId int = 48213;

SELECT TOP (50) o.OrderId, o.OrderDate, o.Total, c.Name
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Open'
  AND (o.OrderDate < @LastDate
       OR (o.OrderDate = @LastDate AND o.OrderId < @LastId))
ORDER BY o.OrderDate DESC, o.OrderId DESC;

The sort key must be unique, hence the tie-breaker (OrderId). The cost is now the same for every page.

OFFSET/FETCH Keyset
Cost of page N grows with N constant
Jump to page 500 yes no (next/previous only)
Stable under inserts no yes
Total row count separate query separate query
Complexity trivial unique key, the client keeps a cursor

If users really need "go to page 500", keep OFFSET but limit the depth (nobody reads page 2,000: offer a search or a filter instead).

The supporting index

Keyset only pays if the index matches the filter and the sort:

CREATE INDEX IX_Orders_Status_Date_Id
ON dbo.Orders (Status, OrderDate DESC, OrderId DESC)
INCLUDE (CustomerId, Total);

Equality column first (Status), then the sort columns in the same direction as the ORDER BY, then the columns needed only for output in INCLUDE. With this index the plan is a seek followed by a read of 50 rows, with no sort operator. If the plan still contains a Sort or a Key Lookup on thousands of rows, the index does not match.

Measuring before and after

Never trust the feeling. Two tools:

SET STATISTICS IO, TIME ON;
-- run the old query, note "logical reads" and "CPU time"
-- run the new query, compare

Logical reads is the metric I trust most: it does not depend on cache warmth. I run each variant twice and look at the second run. For the real workload, Query Store keeps the history per query: average duration, logical reads and execution count before and after the deployment (sys.query_store_runtime_stats). Compare intervals, not single executions, because the list is called with many parameter values.

flowchart LR
  A["Slow list query"] --> B["SET STATISTICS IO : baseline"]
  B --> C["Keyset rewrite"]
  C --> D["Supporting index"]
  D --> E["SET STATISTICS IO : compare"]
  E --> F["Deploy"]
  F --> G["Query Store : check regressions"]

Lessons learned

Mastodon LLIMastodon SocialFosstodon