Providing navigation buttons (prev, next, etc.) on the client side to page through a set of database records is a common task and one that I've had to implement numerous times for different customers. The current customer that I'm working for is using XML based transactions to access data and business logic through the middleware and requires only forward paging through the data. The implementation allows you to pass the maximum number of records you would like back and a cursor indicating what page you are on. The response passes back the actual number of records returned and the cursor to the next page of data.

This works fine as long as you are only asking for forward paging and your data has a sequential key to cursor on like a date or a SQL id.

I'm currently working on a website statistics package for our hosted clients and need to implement paging to allow viewing of the page view data. The data is obviously sorted by date so I do have a sequential cursor that I could use and initially I started to implement the paging code using the date as the cursor. I ran into headaches however as I needed to allow forward and backward paging. This requires remembering the previous cursor and was getting to be a big pain trying to pass those cursors back and forth through my stateless client web pages.

I did some searching and found this page at Microsoft's MSDN website showing a paging technique that I hadn't really considered. They are using nested queries with the SELECT TOP operator to returned back the data pages. The advantage of this technique is that no cursor is required, only the page number that you would like to view - allowing for navigation to the next, previous, first, last, or specific page numbers.

Here's a simplified example of the SQL I'm now using returning my data in descending order (newest record first).

SELECT * FROM
    (SELECT TOP [pageSize] * FROM
        (SELECT TOP [pageSize * pageNumber] * FROM table
            ORDER BY createDt DESC) AS T1
        ORDER BY createDt ASC) AS T2
ORDER BY createDt DESC           

Although it looks complicated, the overhead of the three nested queries doesn't seem to add much processing time. The inner query returns a descending set of records including the first page through the page I'm attempting to retrieve. The next query retrieves the records for the page I'm requesting, by reversing the order and taking the top pagesize records. The outer query simply reverses the order again back to the descending order that I am looking for.

Unfortunately, Microsoft's implementation of SELECT TOP does not allow variable substitution for the TOP operator. So we're forced to build the SQL statements into a string and use the EXEC() command to execute the query. This does add a performance hit, but I couldn't tell you how much. If someone does the analysis I'd appreciate a look at the numbers.

My final implementation of a stored proc (based on the Microsoft example) that takes the page number and page size as parameters is below.

CREATE PROCEDURE retrievePage
    @page int,
    @pagesize int
AS
DECLARE @rowsToRetrieve int, @SQLSTRING nvarchar(1000)
SET @rowsToRetrieve = (@pagesize * @page)
SET NOCOUNT ON
SET @SQLSTRING = N'SELECT * FROM
                       (SELECT TOP ' + CAST(@pageSize as varchar(10)) + ' * FROM
                            (SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) + ' * FROM table
                                 ORDER BY createDt DESC) AS T1
                            ORDER BY createDt ASC) AS T2
                       ORDER BY createDt DESC'
EXEC(@SQLSTRING)
GO