Deterministic paging

This lesson uses DDI_SQL_Training dataset version 1.0.0 on SQL Server 2019 or later. A page boundary is meaningful only if every row has a fixed position. City alone is not unique, so this query uses CustomerID to resolve ties before taking page two.

Run the example

SELECT CustomerID, FirstName, City
FROM dbo.Customers
ORDER BY City, CustomerID
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Read it step by step

1. ORDER BY City, CustomerID first groups cities alphabetically and then fixes order within each city by the unique CustomerID.
2. OFFSET 5 ROWS skips the first five sorted customers: three in Birmingham and two in Bristol.
3. FETCH NEXT 5 ROWS ONLY returns the next five: Cardiff, Leeds, and the first Leicester customer.

Check the result

For the unmodified v1.0.0 dataset, this page always contains the same five customer IDs. It is different from page two ordered only by CustomerID, which contains IDs 6 through 10. Page numbering depends on the exact ORDER BY.

Practice

Page through Customers by CustomerID in groups of five. Write the query for page three and name its IDs.

SELECT CustomerID, FirstName FROM dbo.Customers ORDER BY CustomerID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;