When a list endpoint starts returning too many rows, the first question is usually "offset or keyset?" That question comes second. The first is: can the pagination strategy change later without breaking a single client? If the answer is no, the SQL is the smaller decision, because the response shape has already locked you in.
This guide is a decision analysis for C#, SQL Server and REST APIs, built on documentation from Microsoft. No benchmarks are claimed.
Paging is a contract change before it is a query change
Microsoft's API guidance recommends supporting paging whenever a collection could grow large, and Azure's own API tooling notes that designing pagination up front avoids a later breaking change when continuation links have to be added. That matches how clients behave. A client handed a bare array has no way to know a second page exists, so it silently processes a truncated list.
Three criteria, in order:
- Can the client discover that more data exists? The response needs a next-page link or continuation token from day one.
- Can the mechanism be swapped underneath? That depends on whether the client treats the token as opaque.
- Do any consumers need to jump to page N? This is the only requirement that really forces offset.
The Azure Architecture Center's API implementation guidance says to include a next-page link or continuation token in the response, to leave the link out on the last page, and to preserve filters, sort order and API version in it. A response shaped that way:
{
"value": [ { "id": 1042, "createdAt": "2026-11-01T10:15:00Z" } ],
"nextLink": "/orders?limit=30&cursor=eyJ..."
}
A client can then use one simple loop: keep following the link while it exists. Nothing in that loop mentions offsets, so nothing in it breaks when the server changes how it paginates.
Offset charges you for rows you throw away, and lets page boundaries drift
Offset is attractive because it is a few lines of code. In EF Core it is Skip and Take, which on SQL Server becomes OFFSET ... FETCH NEXT, valid only as part of an ORDER BY. The cost is easy to miss: the engine does not jump to row 40, it produces rows in order and discards the skipped ones. An index that provides the sort order limits the damage, but the work still grows with the offset.
The second cost is correctness. The EF Core pagination documentation recommends keyset pagination, which uses a WHERE clause to skip rows instead of an offset. Offset pages are positions in a moving result set: if rows are inserted or deleted between requests, a client can see a row twice or miss one. The Data API builder documentation makes the same comparison, noting that keyset pagination avoids missing or duplicated rows when data changes between requests.
The Azure guidelines are blunt about the baseline: document that items may be skipped or duplicated across pages unless the operation takes special precautions, such as a time-expiring snapshot. Even a keyset design needs its consistency behavior written down.
Keyset needs a unique total order, and T-SQL makes you spell it out
Keyset pagination remembers the last row returned and asks for rows after it. The sort key therefore has to be unique. If you sort by CreatedAt alone and two rows share a timestamp, a page boundary can fall between them and one disappears. The fix is a tiebreaker, usually the primary key.
SQL Server does not support row-value comparisons such as (CreatedAt, OrderId) < (@a, @b), so the compound condition is written out by hand. The names here are hypothetical:
-- Supporting index: the seek only works if it matches the sort
CREATE INDEX IX_Orders_CreatedAt_OrderId
ON dbo.Orders (CreatedAt DESC, OrderId DESC);
-- Next page: ask for one extra row to learn whether another page exists
SELECT TOP (@PageSize + 1) OrderId, CreatedAt, Total
FROM dbo.Orders
WHERE CreatedAt <= @LastCreatedAt
AND (CreatedAt < @LastCreatedAt OR OrderId < @LastOrderId)
ORDER BY CreatedAt DESC, OrderId DESC;
What the lines do:
- The two predicates together are the cursor. The first keeps the search seekable on the leading index column, and the second resolves ties by comparing the key. A bare
ORbetween the two branches expresses the same thing, but the form above often gives the optimizer an easier seek. Check the execution plan on your own data. ORDER BYuses the same columns and directions as the index, which is what lets the engine seek to the cursor instead of scanning and sorting.TOP (@PageSize + 1)is a common trick. If more rows than the page size come back, there is a next page. Drop the extra row and buildnextLinkfrom the last row you keep. This also means you never need a total-count query.
The first page has no cursor, so it uses the same query without the WHERE clause. Note also that TOP cannot be combined with OFFSET ... FETCH in one query expression on SQL Server. That is fine here, but it matters if someone later tries a hybrid by editing this query in place.
Make the cursor opaque so the strategy stays replaceable
The cursor is where the contract meets the implementation. Microsoft's API guidelines say clients must treat the next-link URL as opaque, that query options cannot change while iterating, and that servers may return a different page size than the one requested.
That opacity is the lever. If the token is an encoded blob holding the last CreatedAt and OrderId, you can later change the sort key, add a field or move a hot collection to a different store without changing the contract. If clients build ?offset=80 themselves, you cannot. Two practical consequences:
- A cursor should encode the filter and sort it was issued under, or the server should reject a cursor that does not match the current request. Otherwise a client that mixes a cursor from one query with parameters from another gets confusing results.
- Treat a decoded cursor as untrusted input. Base64 hides nothing and a client can edit it, so validate it and return a deliberate 400 for a malformed one. The Azure Architecture Center recommends testing minimum, maximum, negative, malformed and excessive pagination inputs.
Random access is the real price of keyset
The honest cost of keyset is not complexity. It is that you cannot jump to page 37. The EF Core documentation says keyset suits interfaces with next and previous navigation rather than random access, suggests asking whether random access is really needed, and describes a hybrid for when it is: keyset for next and previous, offset for the occasional jump. Keyset also gives no total page count for free.
| Approach | Good for | Trade-off |
|---|---|---|
| Offset | Small tables, admin screens with "go to page N" | Cost grows with the offset; rows can repeat or vanish when data changes |
| Keyset | Large or fast-changing collections, exports, sync jobs | No page jumps; needs a unique sort key and a matching index |
| Hybrid | Interfaces that need both next/previous and occasional jumps | Two code paths to test and document |
A human-facing admin screen with a "go to page 5" control is a reasonable place to keep offset. An endpoint feeding other services, exports or sync jobs usually just wants to walk everything once, which is where drift and duplicates hurt. That is a judgment about the trade-off, not a measurement.
A check to run before choosing
- Does the response already carry a next-page link? If not, add it before the collection grows, because adding it later breaks clients.
- Is the sort order total? Add the key as a tiebreaker. Without one, page boundaries are undefined for ties.
- Does an index match the
ORDER BY? Check the execution plan for a seek, not a scan with a sort. - Is the cursor validated? Test malformed, stale and tampered tokens.
- Does a test follow the links to the last page? It catches off-by-one errors in the extra-row logic.
- Is consistency documented? Say what a client sees if data changes mid-walk.
Where to stay cautious
Offset is not wrong. For a small table behind an internal screen it is the cheaper decision. Sources disagree on how bad offset gets and when, and no row count is offered here as a switching point, so measure your own query plan.
The recommendation is narrow: ship a continuation-link response shape and an opaque cursor from the first version of any list endpoint that could grow. Then choose offset or keyset behind it, based on whether someone truly needs page jumps. The mechanism can change later. The contract is the part you cannot take back.
Comments
Post a Comment