原文:http://beyondrelational.com/modules/29/presentations/483/scripts/12983/sql-server-2012-server-side-paging-demo-using-offsetfetch-next.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Apr
SQL Server 2005 引入了 ROW_NUMBER()来提供一种容易的分页方式。SQL Server 2012 添加了另外一种更简单高效的分页方式。
/*
执行脚本去创建这个表并插入数据。
*/
IF OBJECT_ID('Customers','U') IS NOT NULL
DROP TABLE Customers
CREATE TABLE Customers (
CustomerID INT,
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GO
INSERT INTO Customers (
CustomerID, CustomerNumber, CustomerName, CustomerCity
)
SELECT
Number,
REPLACE(STR(Number, 4), ' ', '0'),
'Customer ' + STR(number,6),
CHAR(65 + (number % 26)) + '-City'
FROM master..spt_values WHERE type = 'p'
AND number BETWEEN 0 AND 999
/*
用ROW_NUMBER()的分页示例- SQL Server 2005/2008 版本。
*/
DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10
;WITH cte AS (
SELECT TOP (@page * @size)
CustomerID,
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
*
FROM cte
WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
ORDER BY seq
/*
SQL Server 2012中新添加的分页示例
*/
DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10
SELECT
*,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerID
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;
/*
同时执行这两个版本,打开执行计划,你可以注意到在这个例子中显著的性能差异。
*/