How do you paginate query results efficiently in SQL Server?
Quick Answer
ORDER BY ... OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY is the simple approach, fine for early pages. For deep pagination it degrades because the engine still reads and discards all the skipped rows. Keyset (seek) pagination — WHERE (SortKey, Id) > (@lastSortKey, @lastId) ORDER BY SortKey, Id — stays fast at any depth.
The straightforward way is OFFSET ... FETCH NEXT, and it's fine for the first few pages. The problem is that OFFSET 100000 still makes the engine walk past a hundred thousand rows to throw them away, so deep pages get slow. The scalable technique is keyset pagination: instead of “skip N”, you remember the sort key and id of the last row you showed and ask for rows greater than that. With a matching index it's an index seek every time regardless of depth. The catch is you can only go next and previous, not jump to an arbitrary page — but that's exactly what an infinite-scroll feed wants.
- Always order by a unique, stable key (add
Idas a tiebreaker) or pages overlap/skip rows. - Keyset pagination needs an index matching the
ORDER BY; it can't jump to “page 500” directly, only next/previous — which is what infinite-scroll APIs actually need. - Avoid
COUNT(*)of the whole set on every page if you can — it's often the slowest part.