SQL Server 2000中的高效分页(限制)查询?

Paging of Large Resultsets 获胜者使用的是rowcount。还有一个更复杂查询的通用版本。 但要归功于 贾斯敏·穆哈雷莫维奇 :)

DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

本文包含整个源代码。

请阅读“更新2004-05-05”信息。!

我想一个嵌套的 SELECT TOP n 查询可能是实现它的最有效方法。

SELECT TOP ThisPageRecordCount *
FROM Table
WHERE ID NOT IN (SELECT TOP BeforeThisPageRecordCount ID FROM Table ORDER BY OrderingColumn)
ORDER BY OrderingColumn

替换 ThisPageRecordCount 每页包含项目和 BeforeThisPageRecordCount 具有 (PageNumber - 1) * items-per-page .

当然,在SQL Server 2005中,更好的方法是使用 ROW_NUMBER() CTE中的函数。

查询的效率实际上取决于底层表的结构。如果,假设您有一个名为id的主键,它是一个标识, 和 它是一个聚集索引, 和 您可以假设没有人在上面插入标识,您可以执行如下查询:

从id>@lastpagesid;所在的表中选择top xxx。

这将尽快得到结果。其他一切真正有效率的东西都是这个的变种——也许它不是一个ID——也许你用来翻页的实际上是一个日期,你知道它是独一无二的,但你明白了这一点……这里显示的基于in()的查询可能会工作,但它们不会影响部分聚集或覆盖索引扫描的性能。

我认为您真正拥有的是升级到SQL 2005的一个令人信服的原因。

在SQL 2005中,可以使用以下方法快速轻松地完成此操作:

select ROW_NUMBER() over (order by [MyField]) as rowNum, *
from [MyTable]
where rowNum between @firstRow and @lastRow

如果您真的坚持使用SQL 2000,我会担心-考虑到现在已经是两代人了,微软不会完全支持它更长时间。

恐怕不会有一个最好的方法来做到这一点——所有的解决方案都有点像黑客。

@然而,Petar Petrov的回答可能是最一致的:

• 如果要处理较小表上的聚集索引进行排序,那么asc-desc方法(使用top动态地构建两个排序)可能更快。
• 如果您的数据是相对静态的,并且您的排序是固定的,那么您可以添加自己的rownum字段,在更改排序顺序时进行更新(听起来很糟糕,但对于大型表来说很快)。

我想你每次都在用查询分析器调整几个小时。存储过程在任何方面都不会有太大的区别——查询计划的缓存不太可能成为瓶颈。

这是将对任何表执行分页的通用SQL Server 2000存储过程。存储过程接受表的名称、要输出的列(默认为表中的所有列)、可选的WHERE条件、可选的排序顺序、要检索的页码和每页的行数。

    CREATE PROCEDURE [dbo].[GetPage]
    @pTableName VARCHAR(30),
    @pColumns VARCHAR(200) = '*',
    @pFilter VARCHAR(200) = '',
    @pSort VARCHAR(200) = '',
    @pPage INT = 1,
    @pPageRows INT = 10
    AS

    SET NOCOUNT ON
    DECLARE @vSQL VARCHAR(4000)
    DECLARE @vTempTable VARCHAR(30)
    DECLARE @vRowStart INT
    DECLARE @vTotalRows INT

    SET @vTempTable = '##Tmp' + CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) +
    CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(SS, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(MS, GETDATE()) AS VARCHAR(3))

    SET @vSQL = 'SELECT ' + @pColumns + ', IDENTITY(INT, 1, 1) AS ROWID INTO ' + @vTempTable + ' FROM ' + @pTableName

    IF @pFilter != '' AND @pFilter IS NOT NULL
    SET @vSQL = @vSQL + ' WHERE ' + @pFilter

    IF @pSort != '' AND @pSort IS NOT NULL
    SET @vSQL = @vSQL + ' ORDER BY ' + @pSort

    EXECUTE (@vSQL)

    -- Get the total number of rows selected
    SET @vTotalRows = @@ROWCOUNT

    -- If page number = 0, set it to the first page
    IF @pPage = 0
    SET @pPage = 1

    -- If page number is beyond the last page, set page to the last page
    IF (@pPage * @pPageRows) > @vTotalRows
    BEGIN
    SET @pPage = @vTotalRows / @pPageRows
    IF (@vTotalRows % @pPageRows) != 0
    SET @pPage = @pPage + 1
    END

    SET @vRowStart = ((@pPage - 1) * @pPageRows) + 1
    SET @vSQL = 'SELECT * FROM ' + @vTempTable + ' WHERE ROWID BETWEEN ' + CAST(@vRowStart AS VARCHAR(10)) +
    ' AND ' + CAST((@vRowStart + @pPageRows - 1) AS VARCHAR(10)) + ' ORDER BY ROWID'
    EXECUTE (@vSQL)

    SET @vSQL = 'DROP TABLE ' + @vTempTable
    EXECUTE (@vSQL)

GO

下面是一些如何使用Northwing数据库的示例:

EXECUTE [dbo].[GetPage] 'Customers', '*', '', '', 1, 10
EXECUTE [dbo].[GetPage] 'Customers', '*', '', 'CustomerID DESC', 1, 10

确认一下,这不是我的工作,而是出于礼貌 http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=1055

干杯,约翰