sql server中cte默认不物化，多次引用会导致重复执行、行数估计失真、索引失效，性能常劣于可建索引且支持统计信息的临时表。
SQL Server中CTE不物化是性能翻车的主因
SQL Server 默认把非递归 CTE 当作“语法糖”展开，而不是缓存结果。你写 WITH order_summary AS (SELECT user_id, COUNT(*) FROM orders GROUP BY user_id)，然后在主查询里 JOIN order_summary 两次，执行计划里大概率出现两次 Hash Match 或两次 Clustered Index Scan —— 底层查询真被跑了两遍。
临时表则不同：SELECT user_id, COUNT(*) INTO #order_summary FROM orders GROUP BY user_id 后，数据已落盘、有统计信息、可建索引，后续所有引用都走这个物理结果集。
常见错误现象：报表 SQL 从 CTE 改成临时表后，执行时间从 12 秒降到 1.8 秒，EXPLAIN 显示 CTE 部分重复扫描了 300 万行订单表。
- SQL Server 2019+ 对 CTE 的物化仍非常保守，仅当满足特定条件（如 CTE 被多次引用 + 含聚合 + 外部查询有
OPTION (RECOMPILE)）才可能触发隐式物化 - 没有
MATERIALIZED提示（那是 MySQL 8.0.31+ 的语法），也不能用/*+ Materialize */（那是 PostgreSQL 的 hint） - 想强制物化？只能改用临时表，或加
OPTION (USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))（不稳定，不推荐生产用）
临时表支持索引和统计信息，CTE 完全不支持
当你在 CTE 里做了 GROUP BY user_id, status，再用 WHERE status = 'active' 过滤，SQL Server 无法利用任何索引加速——CTE 没有元数据，优化器对中间结果行数估计常严重失真（比如预估 100 行，实际 50 万行），导致选错连接算法。
而临时表可以：CREATE INDEX IX_user_status ON #order_summary(user_id, status)，后续 JOIN 或 WHERE 都能命中。
- 数据量 > 5 万行且被多次 JOIN 时，临时表的统计信息让执行计划更稳定
- CTE 中若含
ORDER BY+TOP，SQL Server 可能放弃并行，临时表则无此限制 - CTE 不支持
UPDATE/DELETE操作；临时表可以，适合需要中间修正逻辑的场景
CTE 生命周期太短，跨语句复用完全不可行
CTE 只活在单个语句内。你想先算出活跃用户，再基于它做留存分析、再做 RFM 分群——用 CTE 就得写三个独立 WITH 块，每个都重跑一遍基础聚合。临时表只需一次 INSERT INTO #active_users，后面三步都复用。
典型信号：SELECT * INTO #tmp FROM ... 后紧跟 CREATE INDEX 或 UPDATE #tmp SET flag = 1 WHERE ... —— 这种多阶段加工，CTE 语法上就做不到。
- CTE 引用链不能跨批：GO 分隔后，前一个 CTE 立即失效；临时表只要会话没断就一直可用
- 调试时，
SELECT TOP 100 * FROM #tmp直接看中间结果；CTE 必须把整个主查询复制出来改写，效率低还易出错 - 如果某步需人工校验（比如财务对账），临时表可直接
SELECT导出；CTE 没有持久化载体
什么时候该忍着 CTE 的慢，坚持用它？
只有两个硬性理由值得牺牲性能用 CTE：一是递归（WITH org AS (SELECT ..., FROM org)），SQL Server 里这是唯一标准解法；二是多个 CTE 存在强依赖（B 依赖 A 的输出，C 依赖 B），且整条链只被主查询用一次、数据量小（
其他所有“为了让代码好看而用 CTE”的情况，在 SQL Server 里都可能埋下性能隐患。特别是报表类 SQL，一旦数据量增长、并发上升，CTE 的重复计算会被放大成雪崩效应。
真正容易被忽略的点：开发时用测试库（1000 行数据）看不出 CTE 和临时表差异，上线后面对千万级订单表，物化缺失的代价才彻底暴露——别等监控报警了才回头改。
