最近在网上看到不少文章介绍使用SQL Server 2012的新特性：OFFSET/FETCH NEXT 实现分页。多数文章都是引用或者翻译的这一篇《SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT》，。
邀月对此也做了性能测，，不过老外或者邀月的代码都并没有真正显示出OFFSET/FETCH NEXT的性能比起原有的ROW_NUMBER()方式好多少。
我试了下，发现主要是在取COUNT(*)上，如果OFFSET/FETCH NEXT也同时取COUNT(*)，那么执行计划里一样是聚集索引Scan或者表扫描。如果不同时取COUNT(*)，那么性能提升相当可观。
初始化脚本如下，生成200w条记录，用CTE递归插入，邀月的版权，^_^
USE DBAdminGO/*Setup script to create the sample table and fill it withsample data.*/IF OBJECT_ID('Customers','U') IS NOT NULLDROP TABLE CustomersUSE [DBAdmin]GOCREATE TABLE [dbo].[Customers]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [CustomerNumber] [char](8) NULL, [CustomerName] [varchar](50) NULL, [CustomerCity] [varchar](20) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerID] ASC ) ) ON [PRIMARY]GOTRUNCATE table CustomersGODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGO/*****运用CTE递归插入，速度较快，邀月注***********************/WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS(SELECT 1,cast('00000000'as CHAR(8)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))UNION ALLSELECT num + 1,Cast(REPLACE(STR(num, 8), ' ', '0') AS CHAR(8)),cast('Customer ' + STR(num,8) AS NVARCHAR(50)),cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))FROM SeqWHERE num <= 2000000)INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)SELECT CustomerNumber, CustomerName, CustomerCityFROM SeqOPTION (MAXRECURSION 0) /*Server side paging demo using ROW_NUMBER() - SQL Server2005/2008 version.*/DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ON;SET STATISTICS TIME ON;GODECLARE @page INT, @size INT ,@Total int SELECT @page = 700, @size = 10select @Total = COUNT(*) FROM CustomersWHERE CustomerCity IN ('A-City','B-City');WITH cte AS (SELECT TOP (@page * @size)CustomerID,CustomerNumber,CustomerName,CustomerCity,ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq --,COUNT(*) OVER(PARTITION BY '') AS TotalFROM CustomersWHERE CustomerCity IN ('A-City','B-City')ORDER BY CustomerName ASC)SELECT CustomerID,CustomerNumber,CustomerName,CustomerCity,@TotalFROM cteWHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @sizeORDER BY seq;GOSET STATISTICS IO OFF ;SET STATISTICS TIME OFF;GO CPU及IO：
DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 47 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'Customers'. Scan count 5, logical reads 12600, physical reads 1, read-ahead reads 12468, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 483 ms, elapsed time = 1615 ms.(10 row(s) affected)Table 'Customers'. Scan count 5, logical reads 12648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 671 ms, elapsed time = 183 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
2012下，使用OFFSET/FETCH NEXT分页，同样，我把Count(*)的部分修改了以及ORDER BY CustomerName：
/*Server side paging demo using the new enhancements addedin SQL Server 2012*/DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ON;SET STATISTICS TIME ON;GODECLARE @page INT, @size INT,@Total int SELECT @page = 700, @size = 10select @Total = COUNT(*) FROM CustomersWHERE CustomerCity IN ('A-City','B-City')SELECT*,@Total--,COUNT(*) OVER(PARTITION BY '') AS TotalFROM CustomersWHERE CustomerCity IN ('A-City','B-City')ORDER BY CustomerName OFFSET (@page -1) * @size ROWSFETCH NEXT @size ROWS ONLY;GOSET STATISTICS IO OFF;SET STATISTICS TIME OFF;GO
CPU及IO：
DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC execution completed. If DBCC printed error messages, contact your system administrator.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 26 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'Customers'. Scan count 5, logical reads 12600, physical reads 1, read-ahead reads 12468, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 266 ms, elapsed time = 1688 ms.(10 row(s) affected)Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 639 ms, elapsed time = 175 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
CREATE NONCLUSTERED INDEX IX_Customers_CustomerCityON [dbo].[Customers] ([CustomerCity])INCLUDE ([CustomerNumber],[CustomerName])
加完索引后依然是OFFSET/FETCH NEXT的性能要更好。新特性使得分页的性能提升不少，.net程序员们的福音啊。