如果我们还希望获得结果总数(在进行分页之前)，那么在SQL Server 2000、2005、2008、2012中对结果进行分页的最佳方法(从性能角度而言)是什么？

解决方案

我们没有指定语言，也没有指定使用哪个驱动程序。因此，我正在抽象地描述它。

• 创建一个可滚动的结果集/数据集。这要求在桌子上有一个主桌子
• 跳到最后
• 请求行数
• 跳到页面的开始
• 滚动浏览各行，直到页面末尾

获取结果总数和分页是两个不同的操作。为了这个示例，我们假设我们要处理的查询是

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 RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

这将返回原始查询的第1-19行。这里最酷的事情是，尤其是对于Web应用程序，我们无需保留任何状态，除了要返回的行号。

在http://www.codeproject.com/KB/aspnet/PagingLarge.aspx上可以很好地概述不同的分页技术。

我在SQL Server 2000中经常使用ROWCOUNT方法(与ROW_NUMBER相比也可以在2005＆2008中使用)，闪电般快速，但是我们需要确保已排序的列(主要是)的唯一值。

好吧，我在SQL 2000数据库中使用了以下示例查询，它也适用于SQL 2005. 通过使用多列，它为我们提供的功能是动态排序的。
我告诉你...这很强大:)

ALTER PROCEDURE [dbo].[RE_ListingReports_SelectSummary] 

@CompanyID  int,
@pageNumber     int,
@pageSize   int, 
@sort       varchar(200)
AS

DECLARE @sql nvarchar(4000)
DECLARE @strPageSize nvarchar(20)
DECLARE @strSkippedRows nvarchar(20)
DECLARE @strFields nvarchar(4000)
DECLARE @strFilter nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
DECLARE @strFrom nvarchar(4000)
DECLARE @strID nvarchar(100)

If(@pageNumber < 0)
  SET @pageNumber = 1
SET @strPageSize = CAST(@pageSize AS varchar(20)) 
SET @strSkippedRows = CAST(((@pageNumber - 1) * @pageSize) AS varchar(20))-- For    example if pageNumber is 5  pageSize is 10, then SkippedRows = 40.
SET @strID = 'ListingDbID'
SET @strFields = 'ListingDbID,
ListingID,  
[ExtraRoom]
'
SET @strFrom = ' vwListingSummary '

SET @strFilter = ' WHERE
        CompanyID = ' + CAST(@CompanyID As varchar(20)) 
End
SET @sortBy = ''
if(len(ltrim(rtrim(@sort))) > 0)
SET @sortBy = ' Order By ' + @sort

-- Total Rows Count

SET @sql =  'SELECT Count(' + @strID + ')  FROM ' + @strFROM + @strFilter
EXEC sp_executesql @sql

--// This technique is used in a Single Table pagination
SET @sql = 'SELECT ' + @strFields + ' FROM ' + @strFROM +
    ' WHERE ' + @strID +  ' IN ' + 
   '  (SELECT TOP ' + @strPageSize + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + 
             ' AND  ' + @strID + ' NOT IN ' + '
          (SELECT TOP ' + @strSkippedRows + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + @SortBy + ') ' 
   + @SortBy + ') ' + @SortBy
Print @sql 
EXEC sp_executesql @sql

最好的部分是sp_executesql缓存以后的调用，前提是我们传递相同的参数，即生成相同的sql文本。