9

What is the most efficient paging solution using SQL Server 2005 against a table with around 5,000-10,000 rows? I've seen several out there but nothing comparing them.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Caveatrob
  • 12,667
  • 32
  • 107
  • 187
  • 1
    possible duplicate of [ASP.NET, SQL 2005 "paging"](http://stackoverflow.com/questions/1058825/asp-net-sql-2005-paging) – M4N Sep 19 '10 at 19:54
  • Numerous: http://stackoverflow.com/questions/tagged/pagination+sql-server – OMG Ponies Sep 19 '10 at 19:56

3 Answers3

24

For a table that size, use a Common-Table Expression (CTE) and ROW_NUMBER; use a small function to calculate the records to bring back based on @PageNumber and @PageSize variables (or whatever you want to call them). Simple example from one of our stored procedures:

-- calculate the record numbers that we need

DECLARE @FirstRow INT, @LastRow INT
SELECT  @FirstRow   = ((@PageNumber - 1) * @PageSize) + 1,
        @LastRow    = ((@PageNumber - 1) * @PageSize) + @PageSize

;
WITH CTE AS
(
    SELECT [Fields]
           , ROW_NUMBER() OVER (ORDER BY [Field] [ASC|DESC]) as RowNumber 
    FROM [Tables]
    WHERE [Conditions, etc]
)
SELECT * 
       -- get the total records so the web layer can work out
       -- how many pages there are
       , (SELECT COUNT(*) FROM CTE) AS TotalRecords
FROM CTE
WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC
3Dave
  • 28,657
  • 18
  • 88
  • 151
Keith Williams
  • 2,257
  • 3
  • 19
  • 29
  • Do you have to wait for the proc to run once before you can get the total number of pages? – Caveatrob Sep 19 '10 at 20:42
  • Yes - the way we do it is to use the results of the proc to populate a little PagingInfo object which handles the process of writing out the page number links. Assuming you're not doing a crazy number of joins or function calls in the query, this baby should run near-instantly, so it's never been an issue for us. Glad it's working for you :) – Keith Williams Sep 19 '10 at 21:46
  • It doesn't do the count operation over and over for each row, right? – Caveatrob Sep 19 '10 at 23:18
  • It will, but as I understand it the query analyser will have cached the results of the inner CTE expression, so the count expression adds very little noticeable overhead. It will also be executed only for the n rows that you're actually returning. The only way around it would be to store results in a table variable, but that adds memory overhead. – Keith Williams Sep 20 '10 at 12:58
  • Excellent solution! I never thought of adding the count to the inner CTE expression. – Michael Krauklis Dec 09 '10 at 16:45
  • 4
    If you don't need the `TotalRecords` variable, improve performance of this code by adding `TOP` to the inner table`: `SELECT TOP (@PageNumber * @pageSize) [Fields]` You'll only grab the rows that will be needed, instead of the entire table. – Doug S Jul 25 '12 at 22:40
  • Another way to grab the `TotalRecords` count, is explained in this answer: http://stackoverflow.com/a/11352/1145177 – Doug S Jul 25 '12 at 22:55
4

One of the best discussions of various paging techniques I've ever read is here: SQL Server 2005 Paging – The Holy Grail. You'll have to complete a free registration on SQLServerCentral.com to view the article, but it's well worth it.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

Even this should help.. 

SELECT * FROM 
( 
    SELECT Row_Number() OVER(order by USER_ID) As RowID,
    COUNT (USER_ID) OVER (PARTITION BY null) AS TOTAL_ROWS, 
    select name from usertbl
) 
As RowResults WHERE 
RowID Between 0 AND 25

Not sure if its better than @keith version.

Ashish Gupta
  • 14,869
  • 20
  • 75
  • 134
suraj jain
  • 1,012
  • 14
  • 26