Data paging in SQL Server 2005

T-SQL, SQL Server

With the release of SQL Server 2005, a feature has been added that makes paging data much easier. This feature is the ROW_NUMBER function. This function assigns consecutive row numbers for data returned from a query. One requirement of this function is you need to specify a column for ordering. Take a look at the table and query below,

[ tbl_Chair ]
ChairID  ChairName
——-   ———-
2           Aeron
65         Designer
14         Staples
23         Ergonomic
39         Blue

select row_number() over (order by ChairID) as Row, ChairName from tbl_Chair

Row   ChairName
—-   ———-
1       Aeron
2       Staples
3       Ergonomic
4       Blue
5       Designer

Now if I only wanted the 3rd thrugh 5th row, I could say,

select row, name
from (
select row_number() over (order by ChairID) as Row, ChairName from tbl_Chair
)
where row >=3 and row <=5

Now add a temp table to store the number of Chairs in your table to count the number of pages for paging, you have a stored proc like,

CREATE PROCEDURE GetChairs
(
@PageNumber int,
@ProductsPerPage int
)
…

And you have nice paging functionality.

Comments