幾種資料庫分頁查詢的實現與比較

來源: http://blog.csdn.net/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的時候不做這個？