SQL Server 2005中最佳的分页解决方案是什么？

Question

对于一个包含大约5,000-10,000行的表格，使用SQL Server 2005最有效的分页解决方案是什么？我看到了几个选项，但没有比较它们。

- Caveatrob

---

可能是ASP.NET，SQL 2005“分页”的重复问题。 - M4N

众所周知：http://stackoverflow.com/questions/tagged/pagination+sql-server - OMG Ponies

Answer 1

如果有这么大的表格，可以使用公共表达式（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

- Keith Williams

---

是的 - 我们的做法是使用存储过程的结果来填充一个小的PagingInfo对象，该对象处理编写页面编号链接的过程。假设您在查询中没有进行大量的连接或函数调用，那么这个小工具应该会运行得非常快，所以这对我们来说从未成为问题。很高兴它能为您工作 :) - Keith Williams

它会生效，但据我所知，查询分析器将缓存内部CTE表达式的结果，因此计数表达式几乎不会增加任何明显的开销。它也仅在实际返回的n行中执行。唯一的解决方法是将结果存储在表变量中，但这会增加内存开销。 - Keith Williams

很棒的解决方案！我从未想过将计数添加到内部CTE表达式中。 - Michael Krauklis

如果您不需要TotalRecords变量，通过在内部表中添加TOP来提高此代码的性能：SELECT TOP (@PageNumber * @pageSize) [Fields]。这样，您只会获取所需的行，而不是整个表格。 - Doug S

另一种获取TotalRecords计数的方法在这个答案中有解释：https://dev59.com/4XVD5IYBdhLWcg3wXaYd#11352 - Doug S

Answer 2

我曾阅读过的关于各种分页技术的最佳讨论在这里：SQL Server 2005分页-圣杯。您需要在SQLServerCentral.com上完成免费注册才能查看文章，但它非常值得。

- Joe Stefanelli

Answer 3

即使这样也应该有所帮助..

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 的版本更好。

- suraj jain