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.

Posted in Better Coding, SQL
4 comments on “SQL Pagination
  1. shadowradiance says:

    Hey Andrew – good post. The number of “get way too much from the server” queries is pretty sad.

    One minor thing – I think you’re missing a comma after listPrice.

    It’s also mssql specific.

    With postgresql or mysql, you’d just use the LIMIT and OFFSET options.

    Glad to see you’re still rockin the bloggin

  2. Hey Thanks, I have corrected the syntax. Also, thank you for the addition of postgreSQL and mySQL.

  3. Scott says:

    Currently looking for a pagination solution myself and have seen many references to making use of the row_number() function.
    My issues (and hopefully it’s down to a lack of understanding of what happens with the query optimiser) is that using the row_number() solution does nothing more than get the full query recordset each time and add row numbers to the records before doing a subquery on the results to just return the records required for a page.

    i.e. if the query produced 100000 rows then on page 1 of your output, 20 records per page, the db would basically pull together those 100000 rows, add row numbers to them and then basically select rows 1 to 20 for return to the client. Is this correct?
    Similarly, for the second page, again, those 100000 rows are pulled together and the next group of 20 records are returned. etc. etc. Is this correct? Is this very efficient or is the query optimiser able to select each group of 20 records without having to put together the full set of 100000 each time?

    I’m in the unenviable position of trying to implement a paging solution for a search page where the customer can order the results by any one of a number of columns, where most of them are text fields and not indexed.

    I’m currently looking at using the TOP(x) method and sorting/resorting as appropriate but I’m not sure if this is not internally putting together the full recordset each time as above or is in fact more efficient than the row_number method. It’s certainly more complicated…!

  4. I assure you using row_number() is very efficient. The reason you are finding row_number used in so many examples is because it works very well. I have increased speeds of websites drastically using it. In terms how it work in internal optimization; I cannot answer that for you. I had Googled it once but did not find an answer. If you need sql pagination that works well with sorting / resorting routines, the example I gave works very well with no overhead. The key to using any sql pagination is to have your indexes well tuned.