What is the best way to paginate results in SQL Server

Question

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)?

Answer 1

Finally, Microsoft SQL Server 2012 was released, I really like its simplicity for a pagination, you don't have to use complex queries like answered here.

For getting the next 10 rows just run this query:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; 

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned

Key points to consider when using it:

• ORDER BY is mandatory to use OFFSET ... FETCH clause.
• OFFSET clause is mandatory with FETCH. You cannot use ORDER BY ... FETCH.
• TOP cannot be combined with OFFSET and FETCH in the same query expression.

Answer 2

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.