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
as
(
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.