Page one is quick, but jumping far into the result feels different. OFFSET FETCH must process an ordered prefix before returning the requested page. Give it a deterministic order, measure deeper positions, and decide whether numbered pages still match the workload.
Write the Whole Paging Contract
Paging requires a defined filter, sort order, page size, and consistency expectation. OFFSET skips an ordered number of rows, then FETCH returns the next requested rows. The syntax follows ORDER BY. It is straightforward when the user needs numbered pages and modest jumps. It becomes expensive when a request repeatedly skips a large prefix to return a small suffix.
I ask whether the product needs page numbers or simply a next button. Those interfaces suggest different navigation contracts. Which behavior matters when new rows arrive between clicks? SQL syntax does not answer that question. A page number can describe a live position or a stable report snapshot, and those are different things under concurrent changes.
Build a Matching Ordered Sample
Run these temporary examples in one connection. The setup generates fictional orders and a covering index for the displayed fields. GENERATE_SERIES requires SQL Server 2022 or later with compatibility level 160 or higher. The chosen row population is a setup input, not an observed production count or a claim about measured performance.
CREATE TABLE #PageOrder
(
OrderID int NOT NULL PRIMARY KEY,
OrderedAt datetime2(0) NOT NULL,
AmountCents int NOT NULL
);
INSERT #PageOrder(OrderID,OrderedAt,AmountCents)
SELECT value,DATEADD(minute,value/10,CONVERT(datetime2(0),'20250101')),value%10000
FROM GENERATE_SERIES(1,100000,1);
CREATE INDEX IX_PageOrder_Display
ON #PageOrder(OrderedAt DESC,OrderID DESC) INCLUDE(AmountCents);Several rows deliberately share each timestamp. That makes the tie breaker useful rather than decorative. OrderID supplies a unique ending to the sort. Use the same sort definition in the interface, query, and supporting index. A displayed date rounded to a day is not a substitute for the exact ordering values used by the server.
Calculate the OFFSET FETCH Position With Validated Inputs
Page numbers are one based in this example. The offset is page minus one multiplied by page size. Cast before multiplication so an int intermediate does not overflow on a large requested page. Reject unreasonable inputs at the application boundary and bound page size. A client should not be able to request an enormous result merely by changing one number.
DECLARE @page int=1,@page_size int=20;
IF @page<1 OR @page_size NOT BETWEEN 1 AND 200
THROW 50001,'Choose a positive page and an allowed page size.',1;
DECLARE @offset bigint=(CONVERT(bigint,@page)-1)*@page_size;
SELECT OrderID,OrderedAt,AmountCents
FROM #PageOrder
ORDER BY OrderedAt DESC,OrderID DESC
OFFSET @offset ROWS FETCH NEXT @page_size ROWS ONLY;Keep data and parameter types aligned in the real query. An optional customer filter or join can change which index serves the page. A separate total count query also has a cost and a consistency requirement. Decide whether the interface really needs an exact total on every click, rather than letting a convenient page control dictate an expensive database workload.
Make Ties Deterministic
ORDER BY OrderedAt alone leaves the order among equal timestamps unspecified. Different plans or executions can select different tied rows at a page boundary. Add a unique identifier to the order and keep its direction deliberate. That fixes the tie ambiguity for a stable population. It does not freeze a live table while the user reads the current page.
I review the complete ordering before interpreting repeated or missing rows. A unique sort can still shift when someone inserts or deletes data ahead of the requested offset. Updating a sort value moves its row too. Decide whether live browsing accepts that behavior or whether a report needs a stable snapshot. Do not blame ties for every change caused by concurrent writes.
Measure OFFSET FETCH at an Early and a Deep Page
Turn on the actual execution plan and IO statistics. Compare the same projection and filters at two positions. The following queries request the same page size with different prefixes. Save the actual messages and plans from your server. The script provides no invented timing or read count to copy into a capacity decision.
SET STATISTICS IO, TIME ON;
SELECT OrderID,OrderedAt,AmountCents
FROM #PageOrder
ORDER BY OrderedAt DESC,OrderID DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
SELECT OrderID,OrderedAt,AmountCents
FROM #PageOrder
ORDER BY OrderedAt DESC,OrderID DESC
OFFSET 99980 ROWS FETCH NEXT 20 ROWS ONLY;
SET STATISTICS IO, TIME OFF;Inspect rows processed before the returned page, logical reads, and any sort. On my test server the deep page reported far more logical reads than the first page, with the same index and the same page size. A supporting index avoids some work, but the engine still needs to reach the requested offset. Complex plans can process joins or skip through an index differently, so avoid promising that every query touches the whole base row for each skipped position. Measure the actual access path and its scaling behavior.
Narrow the Work Before Fetching Wide Columns
A covering display index helps when the result fields are small. If the page needs a wide payload, consider selecting the page's keys first, then joining those keys to the wider rows. This lets the ordered paging operation use a narrow access path. Keep the same final ORDER BY because a join does not preserve the ordering of its input automatically.
DECLARE @offset bigint=99980;
WITH page_keys AS
(
SELECT OrderID,OrderedAt
FROM #PageOrder
ORDER BY OrderedAt DESC,OrderID DESC
OFFSET @offset ROWS FETCH NEXT 20 ROWS ONLY
)
SELECT o.OrderID,o.OrderedAt,o.AmountCents
FROM page_keys AS p JOIN #PageOrder AS o ON o.OrderID=p.OrderID
ORDER BY p.OrderedAt DESC,p.OrderID DESC;This sample's original index already covers AmountCents, so it is not a promise that the two step version wins here. Evaluate the shape on your actual wide table. Additional joins, residual filters, and sorting can erase the expected benefit. I keep the simpler query when measured evidence shows no practical improvement from a more involved form.
When Seek Navigation Beats OFFSET FETCH
A cursor based approach starts after the last returned ordering values instead of counting from the beginning. It fits a next and previous browsing interface with a stable unique ordering. A matching index lets the query seek near that boundary. It does not provide an immediate arbitrary page number without separate navigation or anchor data.
Keep this choice tied to the interface. OFFSET FETCH remains reasonable for shallow numbered pages and selected administrative screens. Seek navigation becomes attractive for long feeds and repeated deep browsing. Neither design freezes changing data automatically. A bookmark is helpful, but it does not make the rest of the filing cabinet stop moving.
Test the Last Page and the Changing Population
Test no rows, one partial page, an exact page boundary, a request beyond the last page, and many tied sort values. Rehearse concurrent inserts and deletes according to the chosen consistency contract. Check tenant and visibility filters on every page request. A page boundary carries navigation state, not authorization to browse another customer's rows.
I compare realistic deep positions before accepting paging as complete. The first page demonstrates syntax, while the later pages demonstrate the cost curve. For OFFSET FETCH, keep page inputs bounded, sorting unique, and measurements representative. When the interface repeatedly asks the database to count past nearly everything, change the navigation contract before adding hardware to carry the counting.
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.
A page number is not a cheap position, it is a request to skip an ordered prefix.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
