Server Side Paging with SQL Server Code-Named “Denali”

Well, the captain has just approved the use of electronic devices on my return flight from Las Vegas, where I just spent an exciting week speaking at and attending Visual Studio Live! And I’m eager to share some juicy new SQL Server Denali features with you that I demonstrated in my SQL Server workshop on Friday. Denali, in case you don’t already know, is the code name for the next major version of SQL Server (version 11.0, but whether it will be branded as SQL Server 2011 or SQL Server 2012 is still undetermined).

In the next several posts, I’ll share some of the new T-SQL enhancements offered by Denali. You can start playing with these features right now by downloading the Community Technology Preview (currently, CTP1 is the only publicly available release). Although Microsoft’s greatest advances in Denali center on new Business Intelligence capabilities (which they’re now dubbing “Pervasive Insight”), there are still lots of goodies in store for developers in the relational engine. In this post, I’ll describe a new feature that makes it easier than ever to implement server-side paging.

One Page At A Time, Please

Your query may return hundreds, thousands, or even millions of rows, but users can only cope with so much data at a time. They need paged results; that is, they want to view one chunk at a time in “pages”. The page size can vary of course; 10, 25, 50, or 100 results at a time, but any more than that is just not reasonable. Sure you can allow your query to execute and return a complete resultset from the data access layer to your middle-tier, and then deliver only the subset of that resultset for a single page to display in the user interface. But that’s an incredibly wasteful and inefficient approach. Ideally, of course, if one page is all you want, then that one particular page is all you should need to retrieve from SQL Server when you execute your query.

Returning paged query results was difficult to achieve prior to SQL Server 2005. That release of SQL Server introduced a series of ranking functions, including the ROW_NUMBER function that made it feasible to return one page at a time from your query. I’ll first show how to use ROW_NUMBER in SQL Server 2005 and higher to implement server-side paging, and then show you how much easier it is to achieve the same goal using the new OFFSET/FETCH NEXT syntax introduced in SQL Server Denali.

Using ROW_NUMBER (SQL Server 2005)

The ROW_NUMBER function does just what its name implies; it generates a sequential number for each row in the resultset returned by your query. The value returned by the ROW_NUMBER function can then be used in your query’s WHERE clause to limit the resultset to just the desired page. Here’s an example from the AdventureWorks2008R2 database that demonstrates how to use ROW_NUMBER. It returns “page 3” of the query results, which (assuming a page size of 10) are rows 21 through 30:

-- Get 10 rows starting at row 21 (rows 21-30; i.e., page 3)
DECLARE @PageNum int = 3
DECLARE @PageSize int = 10
DECLARE @FirstRow int = ((@PageNum - 1) * @PageSize) + 1
DECLARE @LastRow int = @FirstRow + @PageSize - 1

SELECT *
 FROM
   (SELECT RowNum = ROW_NUMBER() OVER (ORDER BY LastName, FirstName), *
     FROM Person.Person
   ) AS a
 WHERE RowNum BETWEEN @FirstRow AND @LastRow
 ORDER BY LastName, FirstName

And here are the results:

Now this certainly works, but there are two undesirables here. First, it requires you to manufacture the row number as an additional column in your resultset, whether or not you want/need it. Second, the syntax is somewhat contorted; the required use of a nested SELECT statement and multiple ORDER BY clauses (as well as the required alias “AS a”) is both awkward and unintuitive.

Using OFFSET/FETCH NEXT (SQL Server Code-Named “Denali”)

Now take a look at how the same result can be achieved in Denali:

DECLARE @PageNum int = 3
DECLARE @PageSize int = 10
DECLARE @Offset int = (@PageNum - 1) * @PageSize

SELECT *
 FROM Person.Person
 ORDER BY LastName, FirstName
 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY

I don’t see how the syntax could be any simpler than this, do you? Just specify your starting row with OFFSET and your page size with FETCH NEXT. Here are the results:

You can see that this query returns the same “page” as the previous version of the query that used the ROW_NUMBER function, yet it wasn’t necessary to manufacture a row number column to do it, nor did we need to code a subquery. It’s highly probable that your presentation layer is well aware of which row numbers are being display; it is after all in charge of managing the UI concerns of which page is being displayed to the user. Nevertheless, if you still want row numbers returned in your resultset, you can combine the new Denali syntax with ROW_NUMBER as follows if desired (you’ll still be able to achieve this with a single SELECT, but you’ll need to duplicate the ORDER BY clause with OVER):

DECLARE @PageNum int = 3
DECLARE @PageSize int = 10
DECLARE @Offset int = (@PageNum - 1) * @PageSize

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY LastName, FirstName), *
 FROM Person.Person
 ORDER BY LastName, FirstName
 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY

So there you have it. Server side paging made as easy as one could hope for, thanks to the new OFFSET/FETCH NEXT syntax in SQL Server Denali.

About these ads