SQL Server 2012提供的OFFSET/FETCH NEXT与Row_Number()对比测试

huangfen2002

　　在《SQL Server 2012服务端使用OFFSET/FETCH NEXT实现分页》一文中，我引用了《SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT》，原文地址。　作者在文中称，要SQL Server 2012使用OFFSET/FETCH NEXT分页，比SQL Server 2005/2008中的RowNumber()有显著改进。今天特地作了简单测试，现将过程分享如下：
附：我的测试环境为：
SQL Server 2012，命名实例

　　Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0  (Build 6002: Service Pack 2)
　　
　　沿用上文的测试数据库和表：



IF OBJECT_ID('DemoPager2012') IS NOT NULL
DROP DataBase DemoPager2012
GO
CREATE Database DemoPager2012
GO
USE DemoPager2012
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
CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GO
TRUNCATE table Customers
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
/*****运用CTE递归插入，速度较快，邀月注***********************/
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),
cast('Customer ' + STR(num,6) AS NVARCHAR(50)),
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num