在 SQL Server 中对结果进行分页的最佳方法是什么

2021-12-01 00:00:00 performance sql sql-server pagination

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

What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?

推荐答案

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

Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

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

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

In this case, you would determine the total number of results using:

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

...这可能看起来效率低下，但实际上非常高效，假设所有索引等都设置正确.

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

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

Next, to get actual results back in a paged fashion, the following query would be most efficient:

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

这将返回原始查询的第 1-19 行.这里很酷的事情，特别是对于网络应用程序，你不必保留任何状态，除了要返回的行号.

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.

相关文章