Navigating to Non-Adjacent Pages
If you need to provide users with more flexible navigation capabilities that let them request specific pages regardless of the last page they requested (e.g., page 3, page 10, then page 15), you need to implement a different solution—one that can filter the rows of a requested page when given only the page number and page size as inputs. You can implement a client-side solution or a server-side solution. In a client-side solution, you retrieve all table rows sorted and calculate row numbers by incrementing a counter. You cache all table rows, then respond to a page request by calculating the range of row numbers that you need to return. The first row of a page is ((page number-1) x page size) + 1, and the last row is page number x page size. For example, page 3 with a page size of 10 rows contains the rows 21 through 30. The drawbacks of a client-side solution are that it requires a lot of memory resources at the client and it causes a lot of network traffic and wasted resources when the user doesn't need to access more than a few pages.
In a server-side solution, as in a client-side solution, you calculate row numbers (with sequential integers representing the desired sort) and store the row numbers in a temporary table along with the primary keys of the rows. The client application calculates the range of row numbers of the page the user requests, then submits a query that joins the temporary table and the base table to get all the requested attributes (e.g., order date, customer ID). In the temporary table, you can alternatively store all the attributes of interest to the user, not just the row numbers and the primary keys, thereby removing the need for a join. However, storing all attributes in the temporary table requires a lot of tempdb resources, and you need to determine whether you have those to spare.
Let's start finding a solution to a server-side paging need by calculating row numbers for the different orders and storing them along with the order IDs in a temporary table. Many programmers use a technique to calculate row numbers that isn't guaranteed to always work as you expect. The following code (don't run it!) represents a typical attempt to calculate row numbers for each order ID, based on order date and order ID order:
SELECT IDENTITY(int, 1, 1) AS
RowNum, OrderID+0 AS OrderID
INTO dbo.#OrdersRN
FROM dbo.Orders
ORDER BY OrderDate, OrderID;
This technique won't necessarily work as you'd expect because Microsoft doesn't guarantee that SQL Server will calculate IDENTITY values after the sort takes place. Without such a guarantee, the IDENTITY values that SQL Server assigns don't necessarily represent the requested sort. So, you have to use a different technique to calculate row numbers. In my April 2004 T-SQL 2005 Web column, "Calculating Row Numbers in SQL Server 2005" (InstantDoc ID 42302), I mention that a set-based technique to calculate row numbers in SQL Server 2000 is very slow. Until you use SQL Server 2005, you have to use a cursor if you want to calculate row numbers with reasonable performance. Listing 4 shows the cursor-based code you can use to iterate through the sorted orders, calculating row numbers as you go, and storing the row numbers along with the order IDs in a temporary table. After the code creates the temporary table, your application responds to a specific page request, as Listing 5 shows. You need to run Listing 5's code to get a specific page of orders (e.g., page 3) by joining the Orders table and the temporary table that the code in Listing 4 created, filtering the range of rows that appear in the requested page.
Paging Correctly
Paging is necessary when you want to return a query's result set back to the client application in chunks. You can now provide solutions for two navigation techniques: adjacent pages and non-adjacent pages. And using stored procedures provides encapsulation that lets you alter your solutions' implementation without affecting the client application. Also, remember that IDENTITY values won't necessarily correspond to the sort order you specify in the query's ORDER BY clause, so avoid that technique for calculating row numbers based on a certain order.
End of Article
Prev. page
1
[2]
next page -->
