SQL使用OFFSET/FETCH NEXT实现分页对比ROW_NUMBER，性能差异

最新推荐文章于 2025-10-23 16:52:07 发布

原创最新推荐文章于 2025-10-23 16:52:07 发布 · 4.3k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍使用CTE递归循环进行大规模数据插入的方法，并对比分析了row_number与fetchnext在不同数据量下的查询性能，揭示了fetchnext在大数量级数据查询中的优势。

第一步，创建表

CREATE TABLE Customers (
CustomerID INT,
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GO

第二步，编写语句

插入语句

/*使用CTE递归循环插入运用CTE递归插入，速度较快*/
TRUNCATE table Customers
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();

WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,'0000','Customer 0',cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,'0000','Customer 0',
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 10000/*CTE批量递归500万数据量用时一般大概3分钟因个人电脑而已5000000*/
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)

select [500万数据量插入完毕，共花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

查询语句row_number

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY customerid desc) AS 'RowNumber', * FROM Customers
) AS UserInfo
WHERE UserInfo.customerid BETWEEN 5001 AND 5020

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

查询语句fetchnext

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
select * from Customers order by customerid desc offset 5000 rows fetch next 20 rows only
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;