几种数据库分页查询的实现与比较

来源: http://blog.youkuaiyun.com/smartkernel/archive/2008/02/22/2112917.aspx

几种分页查询的实现与比较：个人比较推荐第5种方式，性能比较稳定，语法结构简单

    --【00】初始化数据（执行几分钟就可以产生几百万数据）Person表结构：ID（int 自增列）,Name(nvarchar(50)),Age(int)
    WHILE 1 = 1
    BEGIN
     INSERT INTO dbo.Person (Name,Age) VALUES (NEWID(),Round(Rand()*100,1))
    END

    SELECT * FROM dbo.Person

    --【01】200万数据情况下，测试时用了7秒：临时表的方法
    DECLARE @StartRow INT
    DECLARE @EndRow INT
    SET @StartRow = 100
    SET @EndRow = 110
    SET ROWCOUNT 0
    DECLARE @TempTable TABLE
    (
        ID int IDENTITY PRIMARY KEY,
        PK int
    )

    INSERT INTO @TempTable SELECT ID FROM dbo.Person ORDER BY Name

    SELECT A.* FROM Person AS A JOIN @TempTable AS B ON A.ID = B.PK WHERE B.ID >= @StartRow AND B.ID < @EndRow

    --【02】200万数据情况下，测试时用了4秒：影响行数方法
    DECLARE @StartRow INT
    DECLARE @PageSize INT
    SET @StartRow = 100
    SET @PageSize = 10

    DECLARE @Sort NVARCHAR(50)--与ORDER BY的列类型一致
    SET ROWCOUNT @StartRow

    SELECT @Sort = Name FROM Person ORDER BY Name
    SET ROWCOUNT @PageSize

    SELECT * FROM Person WHERE Name >= @Sort ORDER BY Name

    --【03】200万数据情况下，测试时用了0秒：子查询的方法
    DECLARE @StartRow INT
    DECLARE @PageSize INT
    SET ROWCOUNT 0
    --SET @StartRow = 100（TOP关键字不能使用变量）
    --SET @PageSize = 10（TOP关键字不能使用变量）
    SELECT * FROM Person WHERE ID IN
    (
     SELECT TOP 10 ID FROM Person WHERE ID NOT IN(SELECT TOP 100 ID FROM Person ORDER BY Name)
        ORDER BY Name
    )
    ORDER BY Name

    --【04】200万数据情况下，测试时用了5秒：游标的方法
    DECLARE @StartRow INT
    DECLARE @PageSize INT
    SET @StartRow = 100
    SET @PageSize = 10
    SET ROWCOUNT 0

    DECLARE @ID INT
    DECLARE @TempTable TABLE (ID INT NOT NULL PRIMARY KEY)

    DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
    SELECT ID FROM Person ORDER BY Name

    OPEN PagingCursor

    FETCH RELATIVE @StartRow FROM PagingCursor INTO @ID

    WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @TempTable(ID) VALUES(@ID)
        FETCH NEXT FROM PagingCursor INTO @ID
        SET @PageSize = @PageSize - 1
    END

    CLOSE PagingCursor
    DEALLOCATE PagingCursor

    SELECT A.* FROM Person AS A JOIN @TempTable AS B ON A.ID = B.ID ORDER BY Name

    --【05】200万数据情况下，测试时用了1秒：行号函数的方法（只有SQL Server 2005以上版本支持）
    DECLARE @StartRow INT
    DECLARE @PageSize INT
    SET ROWCOUNT 0
    SET @StartRow = 100
    SET @PageSize = 10

 SELECT * FROM
 (
  SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber,* FROM Person
 ) AS A WHERE A.RowNumber BETWEEN @StartRow + 1 AND @StartRow + @PageSize

综上所述，Sql Server 2005 的 ROW_NUMBER() 行号函数实现了类似 mysql limit 的分页提取办法

性能得到大幅提升，想不清楚微软件怎么在 Sql server 2000的时候不做这个？