What is the best way to paginate results in MS SQLServer

Question

What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008 if you also want to get the total number of results (before paginating)?

Answer 1

What is the maximum number of results you want to support? If it is small enough, let's say 1000, you can select top 1000 and keep the result set cached for the user. Do the pagination in memory.

Answer 2

You didn't specify the language nor which driver you are using. Therefore I'm describing it abstractly.

• Create a scrollable resultset / dataset. This required a primary on the table(s)
• jump to the end
• request the row count
• jump to the start of the page
• scroll through the rows until the end of the page

Answer 3

Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.

Answer 4

I was going to leave this topic alone, because I figured a billion people were going to jump on it, but it wasn't as busy a thread as I thought it would be. There are some articles on using row number and the BETWEEN statement to efficiently do pagination.

http://www.codeproject.com/KB/database/row_number.aspx http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

and to kind of fake row numbers in sql server 2000 this link should give you something to work with: http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

Answer 5

There is a good overview of different paging techniques at http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

I've used ROWCOUNT method quite often mostly with SQL Server 2000 (will work with 2k5 & 2k8 too, just measure performance compared to ROW_NUMBER), it's lightning fast, but you need to make sure that the sorted column(s) have (mostly) unique values.

Answer 6

Well I have used the following sample query in my SQL 2000 database, it works well for SQL 2005 too. The power it gives you is dynamically order by using multiple columns. I tell you ... this is powerful :)

    ALTER PROCEDURE [dbo].[RE_ListingReports_SelectSummary] 

@CompanyID int,
@pageNumber  int,
@pageSize int, 
@sort  varchar(200)
AS

DECLARE @sql nvarchar(4000)
DECLARE @strPageSize nvarchar(20)
DECLARE @strSkippedRows nvarchar(20)
DECLARE @strFields nvarchar(4000)
DECLARE @strFilter nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
DECLARE @strFrom nvarchar(4000)
DECLARE @strID nvarchar(100)

If(@pageNumber < 0)
  SET @pageNumber = 1
SET @strPageSize = CAST(@pageSize AS varchar(20)) 
SET @strSkippedRows = CAST(((@pageNumber - 1) * @pageSize) AS varchar(20))-- For    example if pageNumber is 5  pageSize is 10, then SkippedRows = 40.
SET @strID = 'ListingDbID'
SET @strFields = 'ListingDbID,
ListingID, 
[ExtraRoom]
'
SET @strFrom = ' vwListingSummary '

SET @strFilter = ' WHERE
  CompanyID = ' + CAST(@CompanyID As varchar(20)) 
End
SET @sortBy = ''
if(len(ltrim(rtrim(@sort))) > 0)
SET @sortBy = ' Order By ' + @sort

-- Total Rows Count

SET @sql =  'SELECT Count(' + @strID + ')  FROM ' + @strFROM + @strFilter
EXEC sp_executesql @sql

--// This technique is used in a Single Table pagination
SET @sql = 'SELECT ' + @strFields + ' FROM ' + @strFROM +
    ' WHERE ' + @strID +  ' IN ' + 
   '  (SELECT TOP ' + @strPageSize + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + 
             ' AND  ' + @strID + ' NOT IN ' + '
          (SELECT TOP ' + @strSkippedRows + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + @SortBy + ') ' 
   + @SortBy + ') ' + @SortBy
Print @sql 
EXEC sp_executesql @sql

The best part is sp_executesql caches later calls, provided you pass same parameters i.e generate same sql text.