SQL PaginationPublished: Apr 04, 2012 23:18:29
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
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.
The post SQL Pagination appeared first on Software Developer In London.
Author: Andrew Pallant
Categories: Better Coding, SQL