Tuesday, March 6, 2007

Paging in MS SQL Server: Pain ends here

Paging in SQL Server was a great pain, it does not have a built-in paging mechanism or keywords. (MySQL has the Limit keyword which makes it easy to page results but it's not standard).

Developers had to go either 2 ways:
  1. Page records after retreiving from DB (using ASP.NET datagrid paging for example)
  2. Make a custom paging solution to page results in the server side.
The first solution was easy and supported by the tools, but was not efficient with really large datasets.

The second option can be applied using many techniques, each has its pros and cons, a good discussion about these techniques is:
Paging of Large Resultsets in ASP.NET

In sql server 2005, things made easier using Common table expressions and the new ranking functions, especially: row_number()
Using row_number and Common table expression you can make an efficient SQL Server paging for resultsets.

For example, assume you want to return only the page #30 of the employees table sorted by name, and the page size is 10:

With EmployeesCTE
Select row_number() over (order by Name) as rownum,*
from Employees
Select * from EmployeesCTE
Where rownum between 291 and 300
Order by Name

The first part: (With EmployeesCTE..) creates a common table expression, which can be referenced in subsequent T-SQL statements and you can consider it as an (on the fly) view.
The row_number function returns the record number based on the order specified in the OVER clause.

The second part is the easy one, now you have a simple view (EmployeesCTE) that contains the
record number, you can easily get the range of records you need using: Between x and y.


Anonymous said...

It is easy to get a specified range of record by "between x and y". But how can I determine the max no of rownum ? If I put a number which is out of the range of records, no records will be show. Of course, you can get the recordcount by another query in advance. But is there any other way to do it?

Hesham A. Amin said...

You can select count(*) in the CTE. But I'm not sure about the impact on performance.
Some applications don't allow users to navigate directly to a specific page number, it forces them to go page by page and avoids this issue.

Anonymous said...

Try this