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

关注

原创最新推荐文章于 2026-09-16 15:23:20 发布 4.8k 阅读 AI 写同款· GEO 优化 ›

SQL Server之游标的基础知识打开优快云 APP Copyright © 1999-2020, youkuaiyun.com, All Rights Reserved 登录 SQL Server之游标的基础知识转载 2018-01-13 萧曵丶码龄8年什么是游标：游标是可以在结果集中上下游动的指针。游标的作用： –允许定位到结果集中的特定行。 –从结果集的当前位置检索一行或多行数据。 –支持对结果集中当前位置的行进... 阅读详情

第一步，创建表

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;