Skip to main content

Cloud

Pagination in SQL Server 2012

When dealing with any product that has been around for a while, it’s not uncommon to observe a progression of common tasks becoming less clumsy as the tools mature. In this post, I take a look at how pagination has evolved with SQL Server resultsets in order to highlight TSQL features introduced last year in SQL Server 2012.
Pagination is used to provide a subset of a potentially large resultset to end users and is used both to prevent overwhelming the user with massive amounts of data and conserve resources such as memory and network bandwidth.  For example, if I perform a Bing search for “pagination”, the results page reports over 4.3 million results – for more than I could ever hope to sift through to find something of value.  I am, however, presented with the first 6 results and the ability to move back and forth between pages of data in anticipation of finding something relevant to my query within at most a few pages.
In SQL Server versions prior to 2005, pagination was a bit of a clumsy task that usually involved populating a temporary table (or table variable for small sets of data after SQL 2000 was released) with all the possible results for the query and a sequential number used to determine each record’s position in the results.  Once this temporary table was populated, a query could be performed to select the rows within a certain range in the order.  This got the job done of only sending a page worth of data between client and server, but was quite cumbersome both to read and write and had the performance penalty of writing the results to a temp table (although some gains could be had by only writing the primary key and ordering value to the temp table and then using a join to the source table).
SQL Server 2005 introduced the ROW_NUMBER() function and made pagination a lot simpler.  This function specifies each row’s position in the resultset, allowing it to serve as a built-in replacement to temporary table column used in previous versions.  The challenge with ROW_NUMBER() is that it cannot be used directly in a WHERE clause, making it necessary to use nested queries and providing a solution that is not always easy to read or maintain.  The following code demonstrates using the ROW_NUMBER() solution to retrieve a page of data from the AdventureWorks2012 sample database:

DECLARE @pageIndex INT;
DECLARE @pageSize INT;
SET @pageIndex = 3;
SET @pageSize = 10;
SELECT r.LastName, r.MiddleName, r.FirstName
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY p.LastName, p.FirstName, p.MiddleName, p.BusinessEntityId) AS rowNumber
, p.LastName
, p.MiddleName
, p.FirstName
FROM Person.Person p
) AS r
WHERE r.rowNumber > (@pageIndex * @pageSize)
AND r.rowNumber < ((@pageIndex + 1) * @pageSize) + 1;

SQL Server 2012 greatly simplifies pagination by introducing syntax elements present in other popular database platforms, OFFSET and FETCH.  Simply put, these are used to direct that a certain number of rows in the resultset be skipped before returning a certain number of rows to the caller.  The whole idea of having to artificially generate a sequence which can be used to control paging and of having to essentially duplicate data projected in the inner and outer query goes away and leaves you with something that is succinct and easy to produce, understand, and maintain.  To produce the same results as in the previous example with the new syntax, your query would look like this:

DECLARE @pageIndex INT;
DECLARE @pageSize INT;
SET @pageIndex = 3;
SET @pageSize = 10;
SELECT p.LastName, p.MiddleName, p.FirstName
FROM Person.Person p
ORDER BY p.LastName, p.FirstName, p.MiddleName, p.BusinessEntityID
OFFSET (@pageIndex * @pageSize) ROWS
FETCH NEXT @pageSize ROWS ONLY;

In this post I have taken a brief look at the progression of TSQL syntax supporting pagination and demonstrated the new language features that make the task much more intuitive in SQL Server 2012.  In addition to conveying information about this specific improvement, hopefully I have also encouraged some thought about what else has changed.  A good place to start looking might be the aptly named article “What’s New in SQL Server 2012” on MSDN.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.