Interview Question of the Week #025 – How to Implement Paging in SQL Server?

Paging is a good live-coding interview question because the row boundaries expose small mistakes. My original example used SQL Server 2005 and ROW_NUMBER(); that is still the right context for this answer.

Question: How do you implement paging in SQL Server?

Answer: Give the rows a stable order, number them, and filter for the requested range. In the original AdventureWorks schema, ContactID breaks name ties so the order is deterministic. This derived-table version selects 20 rows, numbered 121 through 140:

USE AdventureWorks2025;
GO
DECLARE @StartRow int, @EndRow int;
SET @StartRow = 120;
SET @EndRow = 140;

SELECT FirstName, LastName, EmailAddress
FROM
(
    SELECT PC.FirstName, PC.LastName, EA.EmailAddress,
           ROW_NUMBER() OVER
           (ORDER BY PC.FirstName, PC.LastName, PC.BusinessEntityID, EA.EmailAddress) AS RowNumber
    FROM Person.Person AS PC
    INNER JOIN Person.EmailAddress AS EA
        ON EA.BusinessEntityID = PC.BusinessEntityID
) AS PersonContact
WHERE RowNumber > @StartRow
  AND RowNumber <= @EndRow
ORDER BY RowNumber;

My old post also showed the same method with a CTE. Here is that form with the same corrected boundary:

USE AdventureWorks2025;
GO
DECLARE @StartRow int, @EndRow int;
SET @StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
    SELECT PC.FirstName, PC.LastName, EA.EmailAddress,
           ROW_NUMBER() OVER
           (ORDER BY PC.FirstName, PC.LastName, PC.BusinessEntityID, EA.EmailAddress) AS RowNumber
    FROM Person.Person AS PC
    INNER JOIN Person.EmailAddress AS EA
        ON EA.BusinessEntityID = PC.BusinessEntityID
)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
  AND RowNumber <= @EndRow
ORDER BY RowNumber;

The original code used RowNumber < @EndRow, which returned only rows 121 through 139. The inclusive upper bound fixes that off-by-one error. On SQL Server 2012 and newer, OFFSET/FETCH is another way to request a page, but it needs the same deliberate ordering. If rows change between page requests, even a unique sort key cannot freeze the underlying data.

For comparisons, see SQL 2000 versus SQL 2005 paging and CTE versus derived-table paging.

Want to try this yourself? Here is how to install AdventureWorks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.