Pagination in SQL Server5
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, language sql, microsoft sql server, sql statements, T-SQL
5 thoughts on “Pagination in SQL Server”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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.
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.
Nice addition. I can see where that would be extremely helpful.
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.
Aaron Bertrand wrote a post recently on pagination performance. http://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch you might look there.