Paging: OFFSET/FETCH vs Keyset

Any report with a grid needs paging. The two standard approaches have very different performance characteristics at depth, and the difference matters as soon as the result set is large.

OFFSET/FETCH

The straightforward option, available since SQL Server 2012:

SELECT  o.OrderId, o.OrderDate, o.CustomerId, o.Total
FROM    dbo.Orders AS o                       -- placeholder table
WHERE   o.OrderDate >= @From AND o.OrderDate < @To
ORDER BY o.OrderDate DESC, o.OrderId DESC
OFFSET  @PageNumber * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Clean, readable, supports jumping to an arbitrary page number, and it is what most grid components generate.

The cost is in how it works: to return rows 100,001 through 100,050, SQL Server produces the first 100,000 rows and discards them. Cost grows linearly with page depth. Page 1 is instant; page 2,000 is not.

For most reports this is acceptable, because users rarely go past the first few pages. It becomes a real problem in two situations: a very large result set with genuine deep paging, and an export loop that walks every page — which turns an O(n) export into O(n²).

The ORDER BY must be deterministic

If the sort columns are not unique, rows can appear on two pages or on none, because the engine is free to order tied rows differently between executions. Always append a unique tiebreaker:

ORDER BY o.OrderDate DESC, o.OrderId DESC   -- OrderId makes the ordering total

This is a correctness issue, not a performance one, and it produces bug reports that are very hard to reproduce.

The index that makes paging cheap

Paging needs the data already in the sorted order, or the engine sorts the whole result set before it can skip anything.

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

With this in place the plan is an ordered index scan with a Top operator — no Sort. Without it, every page request sorts the entire filtered set. Check the actual plan for a Sort operator: if one is there, the index does not match the ORDER BY.

Keyset pagination

Instead of "skip 100,000 rows," say "give me the rows after this one." The client remembers the last row it saw and passes those values back.

-- First page
SELECT TOP (@PageSize)
        o.OrderId, o.OrderDate, o.CustomerId, o.Total
FROM    dbo.Orders AS o
WHERE   o.OrderDate >= @From AND o.OrderDate < @To
ORDER BY o.OrderDate DESC, o.OrderId DESC;

-- Subsequent pages: @LastDate and @LastId come from the last row of the previous page
SELECT TOP (@PageSize)
        o.OrderId, o.OrderDate, o.CustomerId, o.Total
FROM    dbo.Orders AS o
WHERE   o.OrderDate >= @From AND o.OrderDate < @To
  AND ( o.OrderDate < @LastDate
     OR (o.OrderDate = @LastDate AND o.OrderId < @LastId) )   -- the tiebreaker
ORDER BY o.OrderDate DESC, o.OrderId DESC;

Every page seeks directly to its starting position. Page 2,000 costs the same as page 2. Cost is constant with depth.

The compound comparison expresses "strictly after (LastDate, LastId) in the sort order." The row_constructor form (WHERE (OrderDate, OrderId) < (@LastDate, @LastId)) is not supported in T-SQL, so the explicit OR version above is the way to write it.

What you give up: you cannot jump to page 47. Only next and previous. That rules it out for a grid with numbered page links, and makes it ideal for infinite scroll, "load more," and export loops.

Comparing them

| | OFFSET/FETCH | Keyset | |---|---|---| | Cost at page N | grows with N | constant | | Jump to arbitrary page | yes | no | | Total page count | easy (with a count query) | not naturally available | | Rows shifting during paging | can duplicate or skip rows | stable | | Grid component support | universal | needs custom wiring | | Best for | typical grids, shallow paging | deep paging, exports, infinite scroll |

That "rows shifting" row is worth a moment. If data is inserted while a user pages, OFFSET-based paging shifts everything down and the user sees a row twice or misses one. Keyset paging is anchored to a value, so it is stable under concurrent inserts.

Measuring the difference

SET STATISTICS IO, TIME ON;

DECLARE @PageSize int = 50;

-- Deep OFFSET: reads and discards everything before the page
SELECT TOP (@PageSize) object_id, name
FROM (
    SELECT object_id, name,
           ROW_NUMBER() OVER (ORDER BY object_id) AS rn
    FROM sys.all_columns
) AS x
WHERE rn > 5000
ORDER BY rn;

-- Keyset equivalent: seeks straight to the position
DECLARE @LastId int = (SELECT MAX(object_id) FROM (
        SELECT TOP (5000) object_id FROM sys.all_columns ORDER BY object_id) AS y);

SELECT TOP (@PageSize) object_id, name
FROM   sys.all_columns
WHERE  object_id > @LastId
ORDER BY object_id;

SET STATISTICS IO, TIME OFF;

The gap widens as the offset grows. Try it at 5,000 and then at 50,000.

The row-count query

Grids want a total count for the pager. Do not run the full query twice.

-- Count and page in one pass, when the result set is manageable
SELECT  o.OrderId, o.OrderDate, o.Total,
        COUNT(*) OVER () AS TotalRows
FROM    dbo.Orders AS o
WHERE   o.OrderDate >= @From AND o.OrderDate < @To
ORDER BY o.OrderDate DESC, o.OrderId DESC
OFFSET @PageNumber * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

COUNT(*) OVER () gives the total in the same pass. Note that it does require the engine to enumerate the full filtered set, so on a very large result set a separate cached count, or an approximate count, is better. A common compromise: run the exact count once when the filter changes, cache it, and page against it.

The short version

OFFSET/FETCH is fine for shallow paging but it reads and discards every row before the offset, so cost grows with page depth — and an export loop that walks all pages turns into O(n²). Keyset paging uses the last row's key values in the WHERE clause, so every page seeks directly and costs the same. The trade is that you lose arbitrary page jumps. Either way the ORDER BY needs a unique tiebreaker or rows can appear on two pages.