What is the best way to paginate results in SQL Server

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

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 50 AND RowNumber = ‘1980-01-01’ ORDER BY OrderDate

SET ROWCOUNT 0

SELECT t.*
FROM Orders t
INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum

From https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

About Nguyễn Viết Hiền

Passionate, Loyal
This entry was posted in C#, Database, Knowledge, Problem solving, Programming, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s