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;
- /*
- 同时执行这两个版本，打开执行计划，你可以注意到在这个例子中显著的性能差异。
- */