Thursday 27 January 2011

Paging With SQL Server “Denali”

One of the more common tasks in a data-bound application is to display data in a “page-able” user interface – often some sort of data grid. So for example, you might want to create a Web application that shows a list of available products, but which limits the list to ten products at a time. The user then “pages” through the data by clicking “Next Page” or something similar. There are loads of user interface design and implementation patterns that enable this kind of functionality, but they usually either involve fetching all of the data from the database and caching it locally in the application, or implementing some sort of “current page” tracking functionality to adjust the query used to retrieve the data on each page.

SQL Server “Denali” introduces a new way to manage paging within the SELECT statement itself. More specifically, you can use the new OFFSET and FETCH keywords in the ORDER BY clause to limit the query results to a specific page of data. The OFFSET keyword is used to indicate the “starting row” of the results (i.e. the number of rows to skip before this page), and the FETCH keyword is used to indicate the number of pages to be returned (i.e. the page size). For example, the following query skips 20 rows in the underlying dataset and then returns the next 10 rows:

SELECT so.SalesOrderID, so.OrderDate, c.CustomerName
FROM SalesOrder so
JOIN Customer c ON so.Customer = c.CustomerID
ORDER BY SalesOrderID ASC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

For this to work predictably, the ORDER BY clause must include a unique column (or combination of columns) and the underlying dataset must not change between queries.

With this functionality, you can implement an effective paging solution that tracks the position of the first row in the current page, the first row in the next page, and the first row in the previous page. For example, the following stored procedure retrieves the requested page of data based on page size and offset parameter values, and then returns the first row positions for the next and previous pages:

CREATE PROCEDURE GetSalesOrders(@PageSize int, @Offset int,
                                @NextPage int OUTPUT, @PrevPage int OUTPUT)
AS
-- Retrieve the requested page of data
SELECT so.SalesOrderID, so.OrderDate, c.CustomerName
FROM SalesOrder so
JOIN Customer c ON so.Customer = c.CustomerID
ORDER BY SalesOrderID ASC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY

-- Set the row position markers
SET @NextPage = @@ROWCOUNT + @Offset
SET @PrevPage = @Offset - @PageSize
GO

You can then call this stored procedure to navigate forward and backward through the data like this:

DECLARE @StartRow int = 0, @Next int, @Prev int
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT
SET @StartRow = @Next
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT
SET @StartRow = @Next
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT
SET @StartRow = @Prev
EXECUTE GetSalesOrders 10, @StartRow, @Next OUTPUT, @Prev OUTPUT

This code calls the stored procedure 4 times, retrieving the initial page (with an offset of zero), the next two pages, and then the second page again, as shown in the results here:

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
1            2010-01-20 Kasumi Fernandez
2            2010-01-21 Rod Dechamps
3            2010-01-22 Jane Dechamps
4            2010-01-23 Freddy Dechamps
5            2010-01-24 Pierre Dechamps
6            2010-01-25 Kasumi Dechamps
7            2010-02-01 Rod Smith
8            2010-02-01 Jane Smith
9            2010-02-01 Freddy Smith
10           2010-02-01 Pierre Smith

(10 row(s) affected)

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
11           2010-02-01 Kasumi Smith
12           2010-02-01 Rod Jones
13           2010-02-01 Jane Jones
14           2010-02-01 Freddy Jones
15           2010-02-01 Pierre Jones
16           2010-02-01 Kasumi Jones
17           2010-02-01 Rod Yamamoto
18           2010-02-01 Jane Yamamoto
19           2010-02-01 Freddy Yamamoto
20           2010-02-01 Pierre Yamamoto

(10 row(s) affected)

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
21           2010-02-01 Kasumi Yamamoto
22           2010-02-01 Rod Fernandez
23           2010-02-01 Jane Fernandez
24           2010-02-01 Freddy Fernandez
25           2010-02-01 Pierre Fernandez
26           2010-02-01 Kasumi Fernandez
27           2010-02-01 Rod Dechamps
28           2010-02-01 Jane Dechamps
29           2010-02-01 Freddy Dechamps
30           2010-02-01 Pierre Dechamps

(10 row(s) affected)

SalesOrderID OrderDate  CustomerName
------------ ---------- ------------------------------
11           2010-02-01 Kasumi Smith
12           2010-02-01 Rod Jones
13           2010-02-01 Jane Jones
14           2010-02-01 Freddy Jones
15           2010-02-01 Pierre Jones
16           2010-02-01 Kasumi Jones
17           2010-02-01 Rod Yamamoto
18           2010-02-01 Jane Yamamoto
19           2010-02-01 Freddy Yamamoto
20           2010-02-01 Pierre Yamamoto

(10 row(s) affected)

Note that this simple example doesn’t handle the issue of “falling off the end” of the underlying dataset, so an attempt to move forward beyond the last page will return an empty result set, and an attempt to move backward to a position before the first row in the dataset will result in an error (since the OFFSET value cannot be less than zero). You could easily add some basic validation checks in the stored procedure to account for this (for example, resetting @PrevPage to 0 if it becomes negative or setting @NextPage to COUNT(*) - @PageSize if it gets larger than the underlying dataset).

You can download SQL Server “Denali” CTP1 from here, a script to create the sample database I used for the above example from here, and the paging example shown in this article from here.

del.icio.us Tags:

No comments: