SQL Server的两种数据分页方式简析
一、使用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) --排序表达式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