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 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities

However, filtering the records is a little tricky. A TSQL statement like the following will not work.

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities 
WHERE row BETWEEN 25 AND 50

Again, using the ROW_NUMBER directly inside the WHERE clause does not work (as shown below)

SELECT 
     ROW_NUMBER() OVER (ORDER BY City) AS row, 
     CityName, 
     Zip, 
     Country 
FROM Cities 
WHERE ( ROW_NUMBER() OVER (ORDER BY City) AS row ) BETWEEN 50 BETWEEN 75 

One option is to use an inner query:

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

The inner query creates a sub-table and then the outer query filters the records from the inner result set. ROW_NUMBER() is a function that I had been waiting for so long and am glad to find with the version 2005 of SQL Server. As the SQL Server Team celebrates the 17th Anniversary this year, I would really like to congratulate them.

Edit on 16 June 2008

It is almost 2 years since I wrote the above post. I had been learning SQL Server 2005 for the last few years (Still learning uh!) and wrote a few articles and blog posts on the new features introduced by SQL Server 2005. One of the articles that is close to the subject discussed here is "Server side paging with SQL Server 2005". This article closely examines the problems related to server-side-paging and then demonstrates a few examples that implements this.

Edit on 31 July 2010

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 ROWS ONLY;

See Support for Paging Queries in SQL Server CE 4.0 for more details of this feature. I hope this feature will be part of the next SQL Server release.