SQL Server Interview Question #289

What is keyset pagination?

ASP.NET Core, EF Core & SQL Server Real-World Scenarios Senior Advanced

Detailed Explanation

Keyset pagination retrieves the next page based on the last key values from the previous page rather than skipping an increasing number of rows.

For example, when ordering by Id, the next query can request WHERE Id > @LastId ORDER BY Id. For composite ordering, the seek predicate must correctly represent the ordering keys.

Keyset pagination performs particularly well for large sequential datasets, although it is less convenient when users must jump directly to arbitrary page numbers.

Code Example

SELECT TOP (20) Id, Name, Price
FROM dbo.Products
WHERE Id > @LastId
ORDER BY Id;