SQL Server Pagination: OFFSET FETCH vs Keyset Paging

Almost every application needs paging: order lists, search results, audit logs. SQL Server makes it easy with OFFSET ... FETCH, and that works fine for the first few pages. On large tables, though, page 5,000 can be hundreds of times slower than page 1. Keyset paging fixes that.

SQL Server OFFSET FETCH vs keyset pagination

OFFSET FETCH

DECLARE @PageNumber INT = 3, @PageSize INT = 50;

SELECT OrderId, OrderDate, CustomerId, Total
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderId DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
  • ORDER BY is required.
  • Always include a unique column (such as the primary key) at the end of ORDER BY. Without it, rows with the same date can appear on two pages or on none.

Why deep pages get slow

To return page 1,000 with 50 rows per page, SQL Server must read the first 49,950 rows in order and throw them away, then return the next 50. The deeper the page, the more work, even with a perfect index. On a busy table, deep pages also take locks or read versions for all those skipped rows.

Keyset (seek) pagination

Instead of saying "skip N rows", keyset paging says "give me the rows after the last one I saw". The application remembers the sort values of the last row on the current page and passes them back:

DECLARE @LastDate DATETIME2 = '2026-10-20 14:32:00',
        @LastId   INT       = 98231,
        @PageSize INT       = 50;

SELECT TOP (@PageSize) OrderId, OrderDate, CustomerId, Total
FROM dbo.Orders
WHERE OrderDate < @LastDate
   OR (OrderDate = @LastDate AND OrderId < @LastId)
ORDER BY OrderDate DESC, OrderId DESC;

With an index that matches the sort order, SQL Server seeks directly to the right spot and reads only 50 rows, whether it is the 2nd page or the 2,000th.

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

For the first page, simply run the query without the WHERE clause.

Trade-offs

OFFSET FETCHKeyset
Jump to page NYesNo (next/previous only)
Deep page performanceGets slowerStays constant
Rows inserted while pagingCan shift or duplicate rowsStable
ComplexityVery simpleNeeds last-row values from the client

Which to use

  • OFFSET FETCH: admin screens and small tables, or when users really need "go to page 7".
  • Keyset: infinite scroll, "Load more" buttons, APIs that export or sync data, and any large table where users or jobs read deep pages.

Don't count every row on every page

Showing "Page 3 of 12,840" requires counting all matching rows, which can cost more than fetching the page itself. Options:

  • Count once and cache the result, or show an approximate count.
  • Show only "Next" and "Previous" without a total.
  • If you must, use a separate COUNT(*) query with the same filter rather than COUNT(*) OVER() on every row of every page.

Quick checklist

  • Always end ORDER BY with a unique column.
  • Index the sort columns in the same order and direction.
  • Use keyset paging for large tables and deep pages.
  • Test performance on a deep page, not just page 1.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more