Page two feels quick, but page two thousand keeps everyone waiting. Keyset pagination starts after the last row you saw instead of counting past all earlier rows. A matching index and a unique sort order make that next-page request predictable.
Give Keyset Pagination a Unique Sort
Suppose your feed lists the newest items first. PublishedAt alone is insufficient because several items can share a timestamp. Add ItemID as a unique tie breaker and use both values everywhere: ORDER BY, index keys, and continuation predicate. Without the second value, rows sharing the boundary timestamp can disappear between pages.
I check the complete ordering before looking at the plan. Which row comes after another when their timestamps match? If the application cannot answer, neither can a reliable cursor. Keep nullable sort columns out of this first design. A nullable value needs an explicit ordering and comparison rule that the cursor must reproduce.
CREATE TABLE #Feed
(
ItemID int NOT NULL PRIMARY KEY,
PublishedAt datetime2(0) NOT NULL,
Title varchar(60) NOT NULL
);
WITH d AS
(
SELECT v FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS x(v)
), n AS
(
SELECT 1+a.v+10*b.v+100*c.v+1000*d.v+10000*e.v AS rn
FROM d AS a CROSS JOIN d AS b CROSS JOIN d AS c
CROSS JOIN d AS d CROSS JOIN d AS e
)
INSERT #Feed(ItemID,PublishedAt,Title)
SELECT rn,DATEADD(minute,rn/10,CONVERT(datetime2(0),'20250101')),
CONCAT('Item ',rn)
FROM n;
CREATE INDEX IX_Feed_Page
ON #Feed(PublishedAt DESC,ItemID DESC) INCLUDE(Title);Return the First Page
Run the examples in one session so the temporary table remains available. The first page uses TOP with the chosen order. Return the timestamp and ID even if the user interface displays only a title. Those two values form the next cursor. Store their exact types and precision, rather than formatting the timestamp as a localized string.
SELECT TOP (25) ItemID,PublishedAt,Title
FROM #Feed
ORDER BY PublishedAt DESC,ItemID DESC;An application can encode the cursor as an opaque token. Validate it on the server, bind it to the selected filters, and limit page size. A token is navigation state, not authorization. Apply the same tenant and visibility filters on every request. The bookmark does not grant access to the rest of the library.
Keyset Pagination Seeks Past the Last Seen Row
For descending order, the next page contains timestamps below the last timestamp. At the same timestamp, it contains IDs below the last ID. That is a lexicographic comparison expressed as two branches. SQL Server does not accept a row-value inequality like (PublishedAt,ItemID) < (...), so write the conditions explicitly.
DECLARE @last_time datetime2(0), @last_id int;
SELECT @last_time=PublishedAt,@last_id=ItemID
FROM #Feed WHERE ItemID=50000;
SELECT TOP (25) ItemID,PublishedAt,Title
FROM #Feed
WHERE PublishedAt < @last_time
OR (PublishedAt = @last_time AND ItemID < @last_id)
ORDER BY PublishedAt DESC,ItemID DESC;The setup reads one sample boundary by ID. Your application supplies the last values returned by its preceding page. Keep parameters the same types as the columns to avoid conversion on the indexed value. I inspect Seek Predicates and Actual Rows Read. The tuple condition and index must work together; a friendly operator name alone proves little.
Read Backward for the Previous Page
The previous page starts before the current page's first row in display order. Read greater values in ascending order to get the nearest preceding rows. Then sort that small result descending for display. Simply changing the comparison and leaving descending TOP would return the newest page instead of the adjacent previous page.
DECLARE @first_time datetime2(0), @first_id int;
SELECT @first_time=PublishedAt,@first_id=ItemID
FROM #Feed WHERE ItemID=50000;
SELECT ItemID,PublishedAt,Title
FROM
(
SELECT TOP (25) ItemID,PublishedAt,Title
FROM #Feed
WHERE PublishedAt > @first_time
OR (PublishedAt = @first_time AND ItemID > @first_id)
ORDER BY PublishedAt ASC,ItemID ASC
) AS previous_page
ORDER BY PublishedAt DESC,ItemID DESC;
Compare the Same Deep Boundary
OFFSET and FETCH skip an ordered prefix before returning the requested rows. The plan still needs to process that prefix, although the access path affects its cost. Compare logical reads and runtime against a cursor for the same location. Capture an anchor once outside the timed query; do not hide an OFFSET lookup inside each keyset request.
DECLARE @anchor_time datetime2(0), @anchor_id int;
SELECT @anchor_time=PublishedAt,@anchor_id=ItemID
FROM #Feed
ORDER BY PublishedAt DESC,ItemID DESC
OFFSET 74999 ROWS FETCH NEXT 1 ROW ONLY;
SET STATISTICS IO, TIME ON;
SELECT ItemID,PublishedAt,Title FROM #Feed
ORDER BY PublishedAt DESC,ItemID DESC
OFFSET 75000 ROWS FETCH NEXT 25 ROWS ONLY;
SELECT TOP (25) ItemID,PublishedAt,Title FROM #Feed
WHERE PublishedAt < @anchor_time
OR (PublishedAt=@anchor_time AND ItemID<@anchor_id)
ORDER BY PublishedAt DESC,ItemID DESC;
SET STATISTICS IO, TIME OFF;I save both actual plans and the IO messages. The example chooses a deep position, but it supplies no invented timing or read count. Test shallow and deep positions with your real filters. A noncovering index, expensive join, or selective residual filter can dominate either method.
Understand Changes Between Clicks
New rows inserted ahead of a descending cursor do not shift the cursor's boundary as they shift an OFFSET position. That helps a live feed. It does not freeze the data. Deleting rows can shorten a page, and changing a sort value can move a row across the boundary. A fixed sort definition is essential; mutable sort values still need a stated consistency rule.
For an export requiring one stable snapshot, use a deliberate snapshot or materialized result strategy. Holding a database transaction across human page clicks creates its own operational problems. I distinguish browsing from exporting before choosing that contract. They look alike on screen and behave differently under concurrent changes.
Keep Filters With the Cursor
A continuation token should include a version of the sort contract. If a release changes timestamp precision or adds a new sort column, reject old tokens cleanly. Do not reinterpret an old token under new rules. The same applies to security filters. A token created before a tenant switch must not resume the previous tenant's feed.
Consider rows whose sort value changes. A row moved behind your cursor can appear again. A row moved ahead can be missed. An immutable publication timestamp avoids that class of movement, but updates and deletes still change the visible set. Explain the browsing contract and test it with concurrent writes.
The continuation predicate must be combined with the same filters used for page one. Put parentheses around the OR branches before adding an AND tenant filter. Otherwise operator precedence can let one branch bypass the filter. Match the leading index keys to equality filters followed by the sort keys when that fits the workload.
Test duplicate timestamps, an empty final page, and navigation back from the last page. If the user changes sort or filters, discard the cursor and restart. Reusing a date cursor for an alphabetical list is an application error, not a database tuning problem.
Accept the Keyset Pagination Tradeoff
This method does not provide a direct jump to page N. It provides movement from a known boundary. Numbered page jumps need another design, an anchor cache, or an accepted OFFSET cost. Explain that choice in the user interface rather than pretending a cursor knows every page number.
I favor keyset pagination for feeds and next-page workflows. It keeps navigation tied to rows the reader actually saw. Verify the complete sort, measure the real access path, and document how concurrent changes affect the result.
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 cursor is not a page number, it is a bookmark in one fixed ordering.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
