SQL Server的兩種資料分頁方式簡析

在 Sql Server 2012及以上版本裡面，分頁方法中，Offset and Fetch 同 ROW_NUMBER() 比較起來，無論是性能還是文法，都是有優勢的。但是性能方面，優勢并不是太大，兩者的IO 消耗完全相同，隻是在CPU 方面，Offset and Fetch 方面要好一些，但是不明顯。如果對于一個每秒都要處理成千上萬條的分頁Sql語句的DB 來說，Offset and Fetch 在CPU 方面的優勢會比較明顯的，否則，性能的提升并不明顯。

一、使用ROW_NUMBER() OVER()方式

把表中的所有資料都按照一個ROW_NUMBER進行排序，然後查詢ROW_NUMBER @StartRow到@MaxRows之間的行記錄。

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
 
/*
** 擷取指定頁的記錄。
** 隻适用于指定主鍵@PrimaryKey有唯一值，且@SortExpression中隻指定一個字段排序
**/
CREATE PROCEDURE [dbo].[Common_GetPageRecords]
    @StartRow INT,   --起始行（從0開始）
    @MaxRows INT,  --每頁的最大記錄數
    @TableName NVARCHAR(2000),  --表名
    @PrimaryKey NVARCHAR(50),   --主鍵
    @GetFields NVARCHAR(1000),  --要擷取的列
    @SearchConditions NVARCHAR(2000), --搜尋條件
    @SortExpression NVARCHAR(100) --排序表達式
    WITH ENCRYPTION
 AS

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000), @AscOrDesc NVARCHAR(5)

DECLARE @RecordsCount INT, @SortField NVARCHAR(50), @SortFieldValue NVARCHAR(100), @PrimaryKeyValue NVARCHAR(50)

SELECT @SortExpression = LTRIM(RTRIM(@SortExpression)), @SortField = '', @AscOrDesc = ''
IF @@ERROR <> 0 
BEGIN
    ROLLBACK TRANSACTION
    RETURN
END
IF @SortExpression <> '' AND @SortExpression <> @PrimaryKey
BEGIN
    IF UPPER(RIGHT(@SortExpression, 5)) = ' DESC'
    BEGIN
        SELECT @AscOrDesc = ' DESC', @SortField = RTRIM( LEFT(@SortExpression, LEN(@SortExpression) - 5) )
    END
    ELSE 
    BEGIN
        SELECT @AscOrDesc = ''
        IF UPPER(RIGHT(@SortExpression, 4)) = ' ASC'
            SELECT @SortField = RTRIM( LEFT(@SortExpression, LEN(@SortExpression) - 4) )
        ELSE
            SELECT @SortField = @SortExpression
    END
END

SET @SearchConditions = @SearchConditions + CASE WHEN @PrimaryKey = '' THEN '' ELSE CASE WHEN @SearchConditions = '' THEN '' ELSE ' AND ' END +  @PrimaryKey + ' >= -1' END

IF @MaxRows = -1
BEGIN
    SET @SQL = 'SELECT ' + @GetFields + ' FROM ' + @TableName + 
         CASE WHEN @SearchConditions = '' THEN '' ELSE ' WHERE (' + @SearchConditions + ')' END + 
        ' ORDER BY ' + CASE WHEN @SortField = '' THEN @PrimaryKey ELSE 
                    CASE WHEN @SortField = @PrimaryKey THEN @SortExpression ELSE @SortExpression + ', ' + @PrimaryKey END
        END
    EXECUTE (@SQL)
END
ELSE
BEGIN
    SET @StartRow = @StartRow + 1
    
    SET ROWCOUNT @StartRow
        
    SET @SQL = 'SELECT @PrimaryKeyValue = ' + @PrimaryKey + 
        CASE WHEN @SortField = '' OR @SortField = @PrimaryKey THEN '' ELSE ', @SortFieldValue = CONVERT(NVARCHAR(100), ' + @SortField + ', 121)'  END + 
    ' FROM ' + @TableName + (CASE WHEN @SearchConditions = '' THEN '' ELSE ' WHERE ' + @SearchConditions END) + 
    ' ORDER BY ' + CASE WHEN @SortField = '' THEN @PrimaryKey ELSE 
                CASE WHEN @SortField = @PrimaryKey THEN @SortExpression ELSE @SortExpression + ', ' + @PrimaryKey END
    END
    EXECUTE SP_EXECUTESQL @SQL, N'@PrimaryKeyValue NVARCHAR(50) OUTPUT, @SortFieldValue NVARCHAR(100) OUTPUT', 
        @PrimaryKeyValue OUTPUT, @SortFieldValue OUTPUT

    SET ROWCOUNT @MaxRows

    SET @SQL = 'SELECT ' + @GetFields + ' FROM ' + @TableName + 
        ' WHERE (' + CASE WHEN @SortField = '' OR @SortField = @PrimaryKey 
            THEN @PrimaryKey + (CASE WHEN @AscOrDesc = '' THEN ' >= ' ELSE ' <= ' END) + @PrimaryKeyValue
            ELSE @SortField + (CASE WHEN @AscOrDesc = '' THEN ' > ' ELSE ' < ' END) + '''' + @SortFieldValue + ''' OR (' + @SortField + ' = ''' + @SortFieldValue + ''' AND ' + @PrimaryKey + ' >= ' + @PrimaryKeyValue + ')'
            END + ')' + CASE WHEN @SearchConditions = '' THEN '' ELSE ' AND (' + @SearchConditions + ')' END + 
        ' ORDER BY ' + CASE WHEN @SortField = '' THEN @PrimaryKey ELSE 
                    CASE WHEN @SortField = @PrimaryKey THEN @SortExpression ELSE @SortExpression + ', ' + @PrimaryKey END
        END
    EXECUTE (@SQL)

    SET ROWCOUNT 0
END

SET @SQL = 'SELECT @RecordsCount = COUNT(1) FROM ' + @TableName + (CASE WHEN @SearchConditions = '' THEN '' ELSE ' WHERE ' + @SearchConditions END)
EXECUTE SP_EXECUTESQL @SQL, N'@RecordsCount INT OUTPUT', @RecordsCount OUTPUT

SET NOCOUNT OFF
RETURN @RecordsCount
GO      

執行語句示例：

EXEC [Common_GetPageRecords] @StartRow = 0,              
                             @MaxRows = 200,             
                             @TableName = N'Customers',
                             @PrimaryKey = N'CustomerID',
                             @GetFields = N'CustomerID,CustomerNumber,CustomerName,CustomerCity',
                             @SearchConditions = N'CustomerID>1220',  
                             @SortExpression = N'CustomerID asc';        

二、使用OFFSET FETCH NEXT方式（SQL2012以上的版本才支援：推薦使用 ）

使用OFFSET是SQLServer2012新具有的分頁功能，主要功能是從第x條資料開始共取y資料，但是其必須根再Order By後面使用。

SELECT * FROM [dbo].[Customers] ORDER BY customerid asc OFFSET 0 ROWS FETCH NEXT 200 ROWS ONLY      

三、綜合比較

在 Sql Server 2012及以上版本裡面，分頁方法中，Offset and Fetch 同 ROW_NUMBER() 比較起來，無論是性能還是文法，都是有優勢的。

但是性能方面，優勢并不是太大，兩者的IO 消耗完全相同，隻是在CPU 方面，Offset and Fetch 方面要好一些，但是不明顯。如果對于一個每秒都要處理成千上萬條的分頁Sql語句的DB 來說，Offset and Fetch 在CPU 方面的優勢會比較明顯的，否則，性能的提升并不明顯。

文法方面 Offset and Fetch 則是十分的簡潔，一句搞定，比起 Row_Number() 好了太多 ~

同是 Offset and Fetch 并不僅僅可以用來分頁哦，具體其他使用，大家可以自行參考 MSDN