0 votes
1 view
in SQL by (20.3k points)

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

1 Answer

0 votes
by (40.3k points)

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.

 

Related questions

0 votes
1 answer
asked Jul 16, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
+2 votes
1 answer
asked Jun 26, 2019 in SQL by Vishal (107k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...