Paging made easy in MS SQL Server 2005

SQL server 2005 has a new function added to it, Row_Number(). It allows you to easily implement paging when you are getting your data.

Suppose you have a simple query like :

select * from users

To prepare it for paging support, it will become:

select Row_Number() over (order by userID) as RowIndex, * from users

The new column returned called RowIndex will have the Index of the rows (zero based), the first row being 0, the second row being 1 and so on. Usually you return the order based on your primary key (in this case userID) but you can put any order you want.

Let us say we have a page size of 10 and we want page 3 then this means we would want records with indexes between 30 and 40 then this statement becomes:

select * from 
    (select Row_Number() over (order by userID) as RowIndex, * from users) as Sub
    Where Sub.RowIndex >= 30 and Sub.RowIndex < 40

The math behind paging is easy, StartIndex = PageSize * PageNumber

and EndIndex = StartIndex + PageSize. Note how the filter is inclusive on the StartIndex and exclusive on the EndIndex.

You can also use the top statement as well.