使用SQL Server 2005的最佳分页解决方案?

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.

使用SQL Server 2005对大约5000 - 10000行的表使用最有效的分页解决方案是什么?我已经见过几个，但没有比较过。

3 个解决方案

#1

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:

对于这样大小的表，使用通用表表达式(CTE)和ROW_NUMBER;使用一个小函数计算记录，根据@PageNumber和@PageSize变量(或您想调用的任何变量)返回记录。我们存储过程中的一个简单示例:

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

#2

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.

我读过的关于各种分页技术的最佳讨论之一是:SQL Server 2005分页——圣杯。您必须在SQLServerCentral.com上完成一个免费注册，才能查看这篇文章，但这是值得的。

#3

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.

不确定它是否比@keith版本更好。

#1

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

#2

#3