Andrew Pallant

Software & Web Developer

Donate To Support My Blog Donate if this post helped you. Coffee money is always welcomed!

SQL Pagination

Physical Link: 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
       rowNumber=ROW_NUMBER() OVER (ORDER BY descriptionOfGoods),
       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.

Categories: Better Coding, SQL

©2024 LdnDeveloper