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