Pagination in SQL Server

5

January 15, 2015 by Kenneth Fisher

It amazes me how much easier certain tasks have gotten in SQL Server. I was watching the Nov 2014 SQL Skills insiders video on OFFSET and FETCH by Jonathan Kehayias (b/t) and remembered fondly an interview I did many, many years ago. As part of the technical interview I was asked to write a pagination query. A query that would pull, for example, the first 20 rows, then the second 20 rows, then the 3rd 20 rows etc. This type of query is frequently used for websites that display information one page at a time. The first 20 rows is pretty easy. That’s just a TOP operation. It gets considerably trickier when you want that second or third group of 20.

All those many years ago when I did that technical interview this was the method I used.

DECLARE @Offset INT = 100;
DECLARE @Fetch INT = 20;

SELECT EmailAddress
FROM
	(SELECT TOP (@Fetch) EmailAddress
	FROM
		(SELECT TOP (@Offset + @Fetch) EmailAddress
		FROM Sales.vIndividualCustomer
		ORDER BY EmailAddress) x
	ORDER BY EmailAddress DESC) y
ORDER BY EmailAddress;
GO

This was probably not the most efficient method even then, it was just what I came up with for the interview. Honestly I’d never encountered the problem before and in fact I still haven’t run into it more than a handful of times all these years later.

This methods biggest problem is that it gets significantly slower the farther into the list you get. In SQL 2005 things got significantly better with the introduction of ROW_NUMBER().

DECLARE @Offset INT = 100;
DECLARE @Fetch INT = 20;

WITH MyCTE AS (SELECT EmailAddress, 
					ROW_NUMBER() OVER (ORDER BY EmailAddress) 
					AS RowNumber
				FROM Sales.vIndividualCustomer)
SELECT EmailAddress, RowNumber 
FROM MyCTE
WHERE RowNumber > @Offset
  AND RowNumber <= @Offset + @Fetch;
GO

This method is good. It is somewhat complicated and requires a windowing function but that isn’t really a big deal.

In the on-going quest to make things easier/better the OFFSET and FETCH options for the ORDER BY clause was introduced in SQL 2012.

DECLARE @Offset INT = 100;
DECLARE @Fetch INT = 20;

SELECT EmailAddress, 
	ROW_NUMBER() OVER (ORDER BY EmailAddress) AS RowNumber
FROM Sales.vIndividualCustomer
ORDER BY EmailAddress OFFSET @Offset ROWS 
	FETCH NEXT @Fetch ROWS ONLY;
GO

The timing on the last two versions is virtual identical as is the execution plan (if you remove the ROW_NUMBER() from the last one it does simplify the execution plan slightly) but the code is much easier and in a large query it could make a bigger difference.

5 thoughts on “Pagination in SQL Server

  1. Amit Mishra says:

    Good article. This kind of query is more required in JS based frameworks, where you don’t have server side intelligent grids for sorting and pagination. I think we can also pass the sorted column name, to get the sorted resultset.

  2. Jim Wheeler says:

    Good article. As a side note, adding ‘COUNT(1) OVER() AS TotalResults’ to the SELECT statement will return the total number of records found so you can calculate how many pages of information you have.

  3. Mark Holmes says:

    Thanks for the article. Would you happen to have any tips on what to do if the CTE dataset returns 100,000 rows and you just want to page through 50 at a time? Never mind the fact that it’s a bad idea or unwise, but for those business users that page through data as part of their normal activity, how would a DBA optimize the query?
    I know I have some assumptions but I’d like to see your thoughts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,671 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: