Pagination Options in SQL Server

If you’ve ever been combing through Google search results or a list of items on Amazon, you’ve seen paging in action. These sites don’t show you millions of results at once. Instead, they may grab the first 20 results for a page, then the next 20 results when you go to the next page, and so on.

Pagination (or paging) is the process used to return a set of results in pages. When it comes to pagination in SQL Server, you have a couple of different options. Today, we’ll look at three of them. The first being pagination with the use of FETCH/OFFSET, the second using a CTE, and the third with the use of ROW_NUMBER. Let’s look at a brief example of how each works.

For each example, I’m using @PageSize to determine how many results to return and @PageNumber to determine what page of results to return from the StackOverflow2013 database.

Pagination with OFFSET and FETCH

OFFSET and FETCH were added in SQL Server 2012. If we want the 50th page of 200 Users from the StackOverflow2013 database, we can run:

DECLARE @PageSize INT = 200;
DECLARE @PageNumber INT = 50;

SELECT Id, DisplayName, AboutMe, LastAccessDate
FROM Users
ORDER BY DisplayName
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

Pagination with OFFSET and FETCH Plus CTE

There are some query modifications that still use OFFSET and FETCH that may be beneficial. We can use a CTE to grab the Id’s of the page to return and then grab the rest of the data by running:

DECLARE @PageSize INT = 200;
DECLARE @PageNumber INT = 50;

WITH cte AS
(
	SELECT Id
	FROM Users
	ORDER BY DisplayName
	OFFSET @PageSize * (@PageNumber - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY
)
SELECT u.Id, u.DisplayName, u.AboutMe, u.LastAccessDate
FROM Users AS u
WHERE EXISTS (SELECT 1 FROM cte WHERE cte.Id = u.Id)
ORDER BY u.DisplayName;

Pagination with ROW_NUMBER

Lastly, we can ditch OFFSET and FETCH altogether and use ROW_NUMBER:

DECLARE @PageSize INT = 200;
DECLARE @PageNumber INT = 50;

SELECT Id, DisplayName, AboutMe, LastAccessDate FROM 
(
	SELECT Id, DisplayName, AboutMe, LastAccessDate, 
        ROW_NUMBER() OVER(ORDER BY DisplayName) AS RowNum 
	FROM Users
) AS RowNumTest
WHERE RowNum BETWEEN (@PageNumber -1) * @PageSize + 1 
    AND @PageSize * @PageNumber;

What to Use?

So which pagination method is the best? It depends. If you are consistently ordering pages by the same column or columns and can index appropriately, you may be in good shape with any of the methods above. If you’re working with a third party application and you’re prevented from adding helpful indexes, maybe one pagination option will stand out.

If I don’t have any indexes and run the three queries above in order, SQL Server likes the CTE version based on query cost:

The results above take about 15 seconds total to return data. If I add an index on DisplayName, all finish in under a second and have a pretty even query cost:

Query cost isn’t the be-all and end-all but no matter your situation, you may want to do some testing with each to find out what works best.

Thanks for reading!