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.
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.
We Would Appreciate Hearing Your Thoughts