在SQLServer中分页结果的最佳方法是什么

Question

在SQLServer中分页结果的最佳方法是什么如果还想获得结果总数(分页前)，那么在SQLServer 2000、2005、2008、2012中对结果进行分页的最佳方法(性能方面)是什么？

Answer 1

获取结果总数和分页是两种不同的操作。为了这个例子，让我们假设您正在处理的查询是

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

在这种情况下，您将使用以下方法确定结果的总数：

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

.这看起来很低效，但实际上性能相当好，假设所有索引等都已正确设置。

接下来，要以分页的方式返回实际结果，以下查询将是最有效的：

SELECT  *FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResultWHERE   RowNum >= 1
    AND RowNum < 20ORDER BY RowNum

这将返回原始查询的第1-19行。这里最酷的是，特别是对于web应用程序来说，除了要返回的行号之外，您不需要保留任何状态。

Answer 2

令人难以置信的是，没有其他答案提到最快在所有SQLServer版本中执行分页的方法。对于大页码，偏移量可能非常慢，如下所示标杆..在SQL中执行分页的方法完全不同，速度要快得多。这通常称为“查找方法”或“keyset分页”，如这篇博文.

SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()FROM playersWHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)ORDER BY score DESC, player_id DESC

“寻求谓词”

这个@previousScore和@previousPlayerId值是上一页上一条记录的相应值。这允许您获取“下一步”页面。如果ORDER BY方向是ASC，简单地使用>相反。

使用上述方法，如果不首先获取前40条记录，就不能立即跳转到第4页。但通常情况下，你不想跳那么远。相反，您可以获得一个更快的查询，根据索引的不同，该查询可能能够在固定时间内获取数据。此外，无论底层数据是否发生变化(例如，在第1页，而在第4页上)，您的页面仍然是“稳定的”。

例如，这是在Web应用程序中延迟加载更多数据时实现分页的最佳方法。

注意，“find方法”也称为键集分页.

分页前记录总数

这个COUNT(*) OVER()窗口函数将帮助您计算“分页前”的总记录数。如果使用的是SQLServer 2000，则必须对COUNT(*).