Page one is quick, and page five hundred makes the application wait. OFFSET and FETCH skip earlier rows, so paging cost can grow with the requested page number.
Use a Stable ORDER BY With OFFSET and FETCH
Both clauses require ORDER BY. The order should be deterministic. Sorting only by CreatedAt leaves ties when several rows share the same timestamp. Add a unique key such as OrderId. Otherwise a row can appear on two pages or be skipped when the plan or data changes.
I ask whether the application needs a numbered page or simply the next set of results. That answer shapes the solution. Page numbers support jumping to a distant position. A next button works well with keyset pagination.
The sort key needs an index when the workload is important. An index on CreatedAt and OrderId can help the engine produce rows in order. Inspect the actual plan and reads before adding one, because every index also costs writes and storage.
DECLARE @PageNumber int = 2;
DECLARE @PageSize int = 25;
SELECT OrderId, CreatedAt, CustomerId
FROM dbo.Orders
ORDER BY CreatedAt DESC, OrderId DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;Understand the Deep Page Cost of OFFSET and FETCH
OFFSET tells SQL Server how many ordered rows to skip before returning the requested set. An index can make the order efficient, but the engine still has to move past earlier positions. For a very deep page, that can be substantial work to return a small result.
I compare logical reads for an early page and a deep page on the same filtered query. Do not invent a universal page number at which it becomes slow. Table size, sort key, filters, and indexes decide. The workload’s actual navigation pattern matters too.
A count query for total pages can add another expensive scan. If the interface only needs Next and Previous, avoid computing an exact total on every request. The UI requirement should justify the database work.
SET STATISTICS IO ON;
SELECT OrderId, CreatedAt
FROM dbo.Orders
ORDER BY CreatedAt DESC, OrderId DESC
OFFSET 10000 ROWS FETCH NEXT 25 ROWS ONLY;
SET STATISTICS IO OFF;Use Keyset Pagination for Next Page
Keyset pagination stores the last sort key from the current page and asks for rows after it. With descending CreatedAt and OrderId, the next page filters to earlier timestamps or the same timestamp with a smaller ID. An index can seek near that position rather than skipping all prior pages.
The cursor value should include every ORDER BY tie breaker. If it contains only CreatedAt, rows sharing the boundary timestamp can disappear. Pass both values back to the application in a controlled token or explicit parameters.
I test a page boundary with several rows at the same timestamp. That is where a keyset predicate proves itself. The code stays readable when the ordering rule is written once and mirrored in the filter.
DECLARE @LastCreatedAt datetime2(3) = '2025-01-01T12:00:00.000';
DECLARE @LastOrderId bigint = 1000;
SELECT TOP (25) OrderId, CreatedAt, CustomerId
FROM dbo.Orders
WHERE CreatedAt < @LastCreatedAt
OR (CreatedAt = @LastCreatedAt AND OrderId < @LastOrderId)
ORDER BY CreatedAt DESC, OrderId DESC;
Handle New Rows and Changing Data
Page-number queries can shift when new rows arrive before the current offset. A reader can see duplicates or miss rows while moving through pages. Keyset pagination anchors each next page to a seen key, which is more stable for forward navigation. It is not a snapshot of the whole dataset.
If the business requires a consistent snapshot across pages, use a snapshot token, saved result set, or an appropriate isolation design. Holding one database transaction open across a long browsing session is usually not a good answer. Define the required consistency level.
I tell application teams that pagination is a data contract. “Show the next page” and “show page 500 of the exact result I saw earlier” are different requests. Choose the persistence and cost each one deserves.
Combine Filters With the Sort Key
Apply business filters before paging. A query for one customer’s orders should filter CustomerId and then sort that customer’s rows. An index beginning with the filter key and continuing with sort keys can help. A generic index on CreatedAt alone can read far more rows than expected.
Check that the filter is stable across page requests. If a user changes status or date range, discard the old keyset cursor and begin again. The cursor belongs to one exact query definition. Reusing it with different filters can produce confusing gaps.
I review parameter types as well. An implicit conversion on an indexed column can block a useful seek. Match the application parameter to the stored type and inspect the plan for the typical filtered request.
SELECT TOP (25) OrderId, CreatedAt
FROM dbo.Orders
WHERE CustomerId = 42
AND CreatedAt >= '2025-01-01'
ORDER BY CreatedAt DESC, OrderId DESC;Make Backward Navigation Explicit
Keyset pagination is simplest for moving forward. Previous page navigation needs the inverse predicate and sort, then a final reorder for display. Another option is to keep a short history of page boundary tokens in the client. Choose based on the interface, not on a desire to use one query for every button.
Random jumps are where OFFSET and FETCH remain convenient. If readers truly need page 37, use it with a measured cost and sensible limits. A search box or filter can be better than asking the database to count its way to an arbitrary deep position.
I ask which navigation behavior people actually use. Many interfaces display page numbers that nobody clicks beyond the first few. Removing an unused deep jump can make the data path simpler without taking away a real workflow.
Test OFFSET and FETCH Correctness and Reads Together
Build a test set with tied timestamps, new inserts between requests, deleted rows, and changed sort values. Check that the interface’s stated behavior holds. A fast seek that skips a boundary row is a bug. A stable list that takes too long also needs work.
Compare actual logical reads, CPU, and duration for early and deep pages. Include the total count query if the interface runs it. Then inspect indexes and sort operators. I change one part at a time so the improvement can be explained.
OFFSET and FETCH are a clear syntax for page numbers. Keyset pagination is a clear syntax for continuing after a known row. Use each for the behavior it supports, and make the order unique in both cases.
Pagination also needs a stable snapshot of the data if a reader expects a complete walk through a changing list. With OFFSET and FETCH, new rows inserted ahead of a later page can shift entries between requests. Which behavior does the application promise: current results on each page or a consistent export? For the latter, use an appropriate isolation strategy or a materialized work set, then verify the total and page order.
Related reading on this blog: Retrieving N Rows After Ordering Query With OFFSET and How to do Pagination in SQL Server? Interview Question of the Week #111.
Pagination is not just limiting rows, it is defining a stable path through an ordered set.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
