How do you paginate results in SQL Server, and what is keyset pagination?
Quick Answer
ORDER BY … OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY is the simple approach, but it gets slow for deep pages because the engine still reads and discards all skipped rows. Keyset (seek) pagination — WHERE (SortKey, Id) > (@lastKey, @lastId) ORDER BY SortKey, Id — stays fast at any depth by seeking to where the last page ended.
The straightforward way is OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY with an ORDER BY. It works, but it's O(offset) — to get page 500 the engine still reads every row up to that point and throws them away, so deep pagination gets slow. For infinite-scroll or large datasets I use keyset pagination: I keep the sort key of the last row from the previous page and do WHERE (Date, Id) > (@lastDate, @lastId), which seeks straight to the right spot using an index and costs the same no matter how deep you are. The catch is you can't jump to an arbitrary page, and the sort key needs a unique tiebreaker.
- OFFSET/FETCH: easy, supports jumping to page N, but page 5000 means reading ~5000 × pageSize rows first. Fine for shallow pages / admin grids.
- Keyset: you carry the sort key of the last row seen and seek past it with an index. O(pageSize) regardless of page depth. Downside: no random page jumps, and the sort key must be unique (add the PK as a tiebreaker).
- Both need a supporting index on the exact
ORDER BYcolumns. - Always include a stable, unique tiebreaker in
ORDER BY— otherwise rows can shift between pages.