Pagination: OFFSET vs keyset

In one line: OFFSET ... FETCH reads every row it skips, so page 1,000 costs hundreds of times page 1, and it repeats or loses rows when the data changes between pages. Keyset paging (“the 50 after the last one I showed you”) costs the same on every page.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/pagination on GitHub.

What you’ll see

  • Deep pages are slow. In the demo, page 1,000 took 159,530 reads to show 50 rows. Keyset took 217.
  • Users see the same row on two pages, or rows they never see at all.
  • “Page 1 of 20,000” is slow even on page 1. The total count cost 15,000× the page.

Why it happens

  • OFFSET 49950 doesn’t jump to row 49,951. It reads 49,950 rows (with a key lookup each, here) and throws them away.
  • OFFSET means “skip the first N rows as they are now”. Five new rows at the top push five rows from page 1 onto page 2, and the reader sees them twice. Five deleted rows mean five rows nobody ever sees.
  • Ties in the ORDER BY let each page’s plan order the tied rows differently. Paging 20,000 tickets by a three-value Priority column showed 1,240 twice and 1,240 never.

What to do

SELECT TOP (50) ... FROM Demo.Orders
WHERE  OrderDate <= @LastDate AND (OrderDate < @LastDate OR OrderID < @LastID)
ORDER BY OrderDate DESC, OrderID DESC;
  • Page by key. Keep the last row’s key, which the application has already shown, and seek past it, with an index on the sort key. It suits next, previous and infinite scroll, which is what most screens do.
  • Make the order unique: add a unique column to the end of every paged ORDER BY.
  • If you need “go to page N”, use a deferred join. Take the OFFSET over the narrow index, then join back for the columns. Page 1,000 fell from 159,530 reads to 322.
  • Don’t count every row on every page. Show “more results”, cache the count, or use an estimate such as sys.partitions.rows.

What doesn’t help

  • COUNT(*) OVER () on the page query. It counts every row behind the page, on every page view: 3,189,538 reads for page 1.
  • The deferred join as a full fix. It still reads the skipped index rows, and the cost still grows with the page number.

Check your server

-- Modules in this database that page with OFFSET or count with COUNT(*) OVER ().
-- A text search: candidates to review, not proof.
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS module_name,
       UsesOffset    = CASE WHEN m.definition LIKE '%OFFSET%ROWS%FETCH%' THEN 1 ELSE 0 END,
       UsesCountOver = CASE WHEN m.definition LIKE '%COUNT(*) OVER%' THEN 1 ELSE 0 END
FROM sys.sql_modules AS m
WHERE m.definition LIKE '%OFFSET%ROWS%FETCH%'
   OR m.definition LIKE '%COUNT(*) OVER%';

See it happen

The pagination demo pages to page 10,000 with OFFSET, a deferred join and keyset. It changes the data between pages, pages through ties, and prices the total count.

Related: missing-index suggestions · key lookups and the tipping point · catch-all queries

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights