SQL Pagination

I have often encountered the culture of query for thousands of results, estimate the paging and then only display a small segment of the results that were retrieved from the database. This is particularly bad in a web environment. This results in the following issues:

• A large amount of data to be transferred from the database to the website
• Longer than necessary database queries
• Script Errors and timeouts
• Frustrated users/customers

The better approach is to allow the database to fetch only the results that are required for paging. Here is an example of what a query may look like.

Declare @PageNumber int
Declare @PageSize int

--Assume we need page 6 i.e. records from 51-60
Set @PageNumber = 6
Set @PageSize = 10

Select Top(@PageSize) * from
(
Select
       rowNumber=ROW_NUMBER() OVER (ORDER BY descriptionOfGoods),
       productID,descriptionOfGoods,itemNumber,MSRPrice,listPrice,
       totalRows=Count(*) OVER() –-Count all records
    from Products
) A
Where A.rowNumber > ((@PageNumber-1)*@PageSize)

Note: This works best when indexes are up-to-date and configured properly. While this seems obvious, it is also a step that I often see missed.