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.
No comments:
Post a Comment