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

Paginating and getting the total number of results are two different operations:

 Let’s see this example given below:

SELECT*

FROM MyOrder

WHERE order_date >= ‘2019-06-06’ ORDER BY order_date

You can determine the total number of results using the below query:

SELECT COUNT (*)

FROM MyOrder

WHERE order_date >= '2019-06-06'

• It may seem inefficient but is quite performant, assuming all indexes, etc. are properly set up.
• To get the actual results back in a paged fashion, the query listed below would be most efficient:

Query:

SELECT *

FROM (SELECT    ROW_NUMBER() OVER (ORDER BY order_date) AS RowNum, *

 FROM  MyOrder

WHERE     order_date >= '2019-06-06'

   ) AS RowConstrainedResult

WHERE   RowNum >= 1

  AND RowNum < 15

ORDER BY RowNum

The above query will return rows 1-14 of the original query.

 Note: For web apps, apart from the row numbers to be returned, you don't have to keep any state.