最近在网上看到不少文章介绍使用SQL Server 2012的新特性：OFFSET/FETCH NEXT 实现分页。多数文章都是引用或者翻译的这一篇《SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT》，原文地址。
邀月对此也做了性能测，《SQL Server 2012服务端使用OFFSET/FETCH NEXT实现分页》，不过老外或者邀月的代码都并没有真正显示出OFFSET/FETCH NEXT的性能比起原有的ROW_NUMBER()方式好多少。
我试了下，发现主要是在取COUNT(*)上，如果OFFSET/FETCH NEXT也同时取COUNT(*)，那么执行计划里一样是聚集索引Scan或者表扫描。如果不同时取COUNT(*)，那么性能提升相当可观。
初始化脚本如下，生成200w条记录，用CTE递归插入，邀月的版权，^_^
USE DBAdmin
GO
/*
Setup script to create the sample table and fill it with
sample data.
*/
IF OBJECT_ID('Customers','U') IS NOT NULL
DROP TABLE Customers
USE [DBAdmin]
GO
CREATE 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]
GO
TRUNCATE table Customers
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
/*****运用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 ALL
SELECT 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 Seq
WHERE num <= 2000000
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)
2005或者2008下，使用ROW_NUMBER()分页，我把Count(*)的部分修改了：
/*
Server side paging demo using ROW_NUMBER() - SQL Server
2005/2008 version.
*/
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @page INT, @size INT ,@Total int
SELECT @page = 700, @size = 10
select @Total = COUNT(*)
FROM Customers
WHERE 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 Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerName ASC
)
SELECT CustomerID,CustomerNumber,CustomerName,CustomerCity,@Total
FROM cte
WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
ORDER BY seq;
GO
SET 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 added
in SQL Server 2012
*/
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @page INT, @size INT,@Total int
SELECT @page = 700, @size = 10
select @Total = COUNT(*)
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
SELECT
*,@Total--,COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerName
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;
GO
SET 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.
可以看到IO有很大的变化，而CPU略有提升。当然也可以在CustomerCity上加上如下索引：
CREATE NONCLUSTERED INDEX IX_Customers_CustomerCity ON [dbo].[Customers] ([CustomerCity]) INCLUDE ([CustomerNumber],[CustomerName])
加完索引后依然是OFFSET/FETCH NEXT的性能要更好。新特性使得分页的性能提升不少，.net程序员们的福音啊