SQL Server 2005 – Data Paging

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

There is one common technique used by most developers to induced qualified records into a temporary table and show paginated records from there. You can learn that approach from 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 temp table for paginated result create substantial overheads to database, which leads to performance issue down the line.

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

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

Which will provide result 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 qualify records from whole table. How?

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 hope you will find this new SELECT extension helpful for all paginated resultset.