We did performance tests on several methods of paging implementation and found the fastest one:

DECLARE @PageNumber INT
DECLARE @PageSize INT
SET @PageNumber = 2
SET @PageSize = 10
SELECT TOP (@PageSize) * FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY FieldA) As RowID,
FieldB,FieldC,
TotalRows=COUNT(*) OVER()
FROM TheTable
) TmpTable
WHERE TmpTable.RowID > ((@PageNumber-1)*@PageSize)

One significant advantage of this implementation approach is that there is no need for a second query that returns the total count of the paged data. The total record count is returned as a column in the result set. This minimizes the number of queries that are needed to return a paged result set. If not needed, the “TotalRows” column can be removed from the query.