Wednesday, April 09, 2008 5:32 PM
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 :
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.