SQL Server 2012 OFFSET/FETCH NEXT分页示例

最新推荐文章于 2025-01-07 10:28:23 发布

翻译最新推荐文章于 2025-01-07 10:28:23 发布 · 置顶 · 1w 阅读

文章标签：

#sql server #table #insert #object #脚本 #null

SQL--(探讨) 同时被 2 个专栏收录

36 篇文章

订阅专栏

SQL Server 2012

5 篇文章

订阅专栏

本文介绍SQL Server 2005及以后版本中的两种分页方法：使用ROW_NUMBER()函数和SQL Server 2012新增的OFFSET FETCH NEXT子句。通过示例对比这两种方法，并分析它们在性能上的差异。

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