Optimized paging query implementation for Microsoft SQL Server 2005 

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.

Teaser: 

Paging queries or paging records is an often-used approach in web applications. It displays subsets of data records as a page, allowing the user to navigate more easily through all pages. The straightforward implementation of a paging query running over large data sets in a stored procedure in MS SQL 2005 can cause increased server load when large numbers of paging queries are executed simultaneously.