在SQL Server 2022中如何使用OFFSET FETCH进行分页查询-SQL-PHP中文网

offset/fetch 必须紧随最外层 order by 之后，且排序字段须为子查询输出的原始列；offset 参数仅支持整型变量或常量，不支持表达式；大偏移量会导致全序遍历性能骤降，应优先考虑键集分页或覆盖索引。

在sql server 2022中如何使用offset fetch进行分页查询

OFFSET 和 FETCH 在 SQL Server 2022 中完全可用，且是官方推荐的分页方式——但它不是“写了就能跑”，稍有疏忽就会报错或性能崩盘。

必须带 `ORDER BY`，否则直接报错

SQL Server 强制要求 OFFSET/FETCH 只能跟在 ORDER BY 后面，连子查询里的 ORDER BY 都不算数。常见错误写法：

SELECT id, name FROM users 
WHERE status = 1 
ORDER BY created_at DESC 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

✅ 没问题——ORDER BY 在最外层，字段 created_at 存在且可排序。

❌ 报错：Invalid usage of the option NEXT in the FETCH statement：

SELECT * FROM (
  SELECT id, name FROM users WHERE status = 1 ORDER BY created_at DESC
) t 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

原因：子查询里的 ORDER BY 对外层 OFFSET 无效，外层没 ORDER BY 就不合法。

必须把 ORDER BY 拉到整个语句最末尾（FETCH 前）
排序字段得是子查询结果集中明确存在的列，不能是别名、函数结果或计算列（除非该计算列有索引）
如果用 CTE，ORDER BY 也必须落在最终 SELECT 上，不能只在 CTE 定义里

`OFFSET` 参数只接受变量或字面量，不支持表达式

想动态算偏移量？别直接写 OFFSET (@page - 1) * @size ROWS——SQL Server 会报语法错误。

正确做法是先算好再代入：

DECLARE @offset INT = (@page - 1) * @size;
SELECT id, title FROM posts 
WHERE published = 1 
ORDER BY publish_time DESC 
OFFSET @offset ROWS FETCH NEXT @size ROWS ONLY;

@offset 和 @size 必须是整型变量或常量（如 10），不能是 LEN(@x)、ISNULL(...) 这类表达式
如果从存储过程传参，建议在开头统一计算 @offset，避免重复计算或类型隐式转换失败
注意 OFFSET 0 ROWS 是合法的，但 OFFSET -1 ROWS 会报错：值必须 ≥ 0

大偏移量下性能急剧下降，不是语法问题而是执行逻辑问题

OFFSET 100000 ROWS 不是“跳到第10万行开始读”，而是让 SQL Server 先扫描、排序、生成前 100000 行，再丢弃——它不会跳过物理页，只会跳过逻辑行。

即使 ORDER BY 字段有索引，SQL Server 仍需遍历索引前 10 万条键值，无法 Seek 到中间位置
当 OFFSET 超过几万行，响应时间可能从毫秒级升到秒级，CPU 和 I/O 压力陡增
没有索引覆盖 ORDER BY + 所有 WHERE 条件时，性能恶化更剧烈（比如 WHERE category = ? ORDER BY created_at，但只有 created_at 单列索引）

缓解方法不是改写 OFFSET，而是换策略：对深度分页场景，优先考虑键集分页（WHERE id > @last_id ORDER BY id LIMIT 10），或加覆盖索引（INCLUDE 所需查询列）。

嵌套查询分页时，过滤条件必须下推，排序字段必须裸露

很多人用子查询是为了先过滤再分页，但写错就白忙活：

-- ❌ 错误：WHERE 放在外层 → OFFSET 实际跳过的是全表行数
SELECT * FROM (
  SELECT id, title, created_at FROM posts
) t 
WHERE t.status = 'published' 
ORDER BY t.created_at DESC 
OFFSET 50000 ROWS FETCH NEXT 20 ROWS ONLY;

上面语句会让 SQL Server 先对全表 posts 排序编号，再过滤——哪怕只有 1000 条已发布文章，OFFSET 50000 仍要扫完整张表。

✅ 正确写法：

SELECT * FROM (
  SELECT id, title, created_at 
  FROM posts 
  WHERE status = 'published'  -- 条件必须进子查询
) t 
ORDER BY t.created_at DESC    -- 字段必须原样出现在子查询输出中
OFFSET 50000 ROWS FETCH NEXT 20 ROWS ONLY;