Paging in SQL Server 2005/2008

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;