SQL Server Interview Question #54

How is pagination implemented with OFFSET and FETCH?

SELECT, Filtering, Sorting & Built-in Functions Mid-Level Intermediate

Detailed Explanation

OFFSET skips a specified number of rows and FETCH NEXT returns the next number of rows. It requires ORDER BY and is commonly used for application pagination.

For very large page numbers, offset pagination can become expensive because SQL Server may still need to process many preceding rows. Keyset or seek pagination can be more efficient for large datasets when the UI and ordering requirements allow it.

Code Example

DECLARE @PageNumber INT = 3,
        @PageSize   INT = 20;

SELECT Id, Name, Price
FROM dbo.Products
ORDER BY Id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;