In order to do paging in SQL Server 2005 or later, we need to use a Common Table Expression (CTE). Below is a practical example.

Regular SQL query without paging:

select *
from Person.Contact
order by FirstName, LastName;

Query as a CTE without paging:

with Persons as
(
	select *
	from Person.Contact
)
select *
from Persons
order by FirstName, LastName;

Query as a CTE with paging, retrieving row number 41 to 60:

with PagedPersons as
(
	select *,
		row_number() over (order by FirstName, LastName) as RowNumber
	from Person.Contact
)
select *
from PagedPersons
where RowNumber between 41 and 60
order by FirstName, LastName;

Convert between PageNumber/RowsPerPage and FirstRow/LastRow:

declare @PageNumber int
declare @RowsPerPage int
declare @FirstRow int
declare @LastRow int

set @PageNumber = 3
set @RowsPerPage = 20

set @LastRow = @PageNumber * @RowsPerPage
set @FirstRow = @LastRow - @RowsPerPage + 1;

with PagedPersons as
(
	select *,
		row_number() over (order by FirstName, LastName) as RowNumber
	from Person.Contact
)
select *
from PagedPersons
where RowNumber between @FirstRow and @LastRow
order by FirstName, LastName;

This entry was posted on Sunday, May 9th, 2010 at 11:03 pm and is filed under SQL Server, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.