SQL Server 2005 – Data Paging

It has now become the standard UI pattern to display large amounts of records in a paginated grid layout. For example, if there is a huge list of employees, the application will show only 10 employees at a time. If the user wants to see the next set of employees, he/she can click on the next button of the grid; the application will pull the next set of 10 employees and render it on the page. By showing a limited number of records, you can control the amount of payload moved from server to browser, saving server engagement and consumed bandwidth.

There is one of the common techniques used by most developers store qualified records in a temporary table and show paginated records from there. You can learn that approach from the following links:

Manual Paging, part I
http://mceahern.manilasites.com/dotnet/pagingpart1

Paging: Use ADO, getrows, or a Stored Procedure?
http://www.15seconds.com/issue/010308.htm

Is Paging with Recordsets the Best Method?
http://www.15seconds.com/issue/010607.htm

The problem with creating a temp table for paginated result create substantial overheads to the database, which leads to performance issue down the line.

But now in SQL Server 2005, you don’t need to create those temp tables. Microsoft extent SELECT statement with new ranking function ROW_NUMBER(), which returns row number in the result set itself.

SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders

Which will provide results as something like:

RowNumber            OrderID     OrderDate
-------------------- ----------- -----------------------
1                    10248       1996-07-04 00:00:00.000
2                    10249       1996-07-05 00:00:00.000
3                    10250       1996-07-08 00:00:00.000
4                    10251       1996-07-08 00:00:00.000
5                    10252       1996-07-09 00:00:00.000
6                    10253       1996-07-10 00:00:00.000
7                    10254       1996-07-11 00:00:00.000
8                    10255       1996-07-12 00:00:00.000
9                    10256       1996-07-15 00:00:00.000
10                   10257       1996-07-16 00:00:00.000

So now you can utilize row number to get qualified records from the whole table. How? Here I go

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber between 21 and 30

Above SQL snippet will display records from 21st to 30th. The result will look something like this:

RowNumber            OrderID     OrderDate
-------------------- ----------- -----------------------
21                   10268       1996-07-30 00:00:00.000
22                   10269       1996-07-31 00:00:00.000
23                   10270       1996-08-01 00:00:00.000
24                   10271       1996-08-01 00:00:00.000
25                   10272       1996-08-02 00:00:00.000
26                   10273       1996-08-05 00:00:00.000
27                   10274       1996-08-06 00:00:00.000
28                   10275       1996-08-07 00:00:00.000
29                   10276       1996-08-08 00:00:00.000
30                   10277       1996-08-09 00:00:00.000

I am sure this new SELECT extension will help a lot to work on the paginated resultset.