Server-side paging with Row_number()

If you are a programmer working with SQL Server, you must have found it little embarrassing to display information which spans across multiple pages (web pages). SQL Server 2000 did not allow you to retrieve a specific range of records, say, records 51 to 100 ordered by a certain column.

For example, assume that you are working on a web page which lists the names of all the cities in different countries. Assume that you need to display 25 records in a page. The database has 50,000 records consisting all the cities/towns across the globe. In the above scenario, it really makes sense to retrieve only the required records. for example, in the first page, retrieve 1 to 25 records. When the user clicks on "next" button, retrieve records 26 to 50 and so on. at this stage the user might click on another column to change the sort order. Earlier it was ordered by city name but now the display is based on Zip code.

With SQL Server 2000, it was not very easy to achieve this. Some times people used temp tables achieve this. Others put the paging responsibility to the application which retrieved all the records and then displayed the information needed for the current page. (this approach will not only overload server resources, but also degrades performance of the application as well as the database server.)

SQL Server 2005 introduces a helpful function ROW_NUMBER() which helps in this scenario. Using ROW_NUMBER()  you can generate a sequence number based on a given sort order and then select specific records from the results. Here is an example:

ROW_NUMBER() OVER (ORDER BY City) as Seq
The syntax ideally says the following. "Order the records by City, and then assign a serial number to each record". You can use it in a query as follows.

SELECT * FROM
(
    SELECT  ROW_NUMBER() OVER (ORDER BY City) AS row, *
    FROM Cities
) AS a WHERE row BETWEEN 101 AND 125

SQL Server CE 4.0 introduced a new TSQL extension that makes paging queries much easier. For example, to fetch rows 21 to 30, a query can be written like this.

SELECT * FROM Orders ORDER BY OrderID
OFFSET 20 ROWS
FETCH NEXT 10 ROW