TOP and OFFSET/FETCH

This lesson uses DDI_SQL_Training dataset version 1.0.0 on SQL Server 2019 or later. A page may show only a few rows at a time. SQL Server offers TOP for a first slice and OFFSET/FETCH for later pages.

Run the example

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

Read it step by step

1. ORDER BY CustomerID establishes a stable sequence from ID 1 through ID 20. CustomerID is unique, so no two rows tie.
2. OFFSET 5 ROWS skips IDs 1 through 5 in that sequence.
3. FETCH NEXT 5 ROWS ONLY returns the next five rows: IDs 6 through 10. OFFSET/FETCH is part of ORDER BY in SQL Server.

Check the result

The result contains exactly five customers because this dataset has at least ten. For the first page, use OFFSET 0 and FETCH NEXT 5; for the third, use OFFSET 10. TOP (5) with the same ORDER BY returns the first page.

Practice

Return the first three Products by ascending ProductID using TOP. Which IDs appear?

SELECT TOP (3) ProductID, ProductName FROM dbo.Products ORDER BY ProductID;