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

对于具有此大小的表,请使用公用表表达式(CTE)和行数;使用小函数计算要基于其返回的记录 @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

我所读过的各种分页技术的最佳讨论之一是: SQL Server 2005 Paging – The Holy Grail . 您必须在上完成免费注册 SQLServerCentral.com 看这篇文章,但它很值得。

即使这样也会有所帮助。

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

不确定它是否优于@keith版本。