为何完整CTE比单独执行CTE a更快？SQL Server执行机制疑问

我在SQL Server里写了一个CTE查询，能正确生成任意时间点各产品的最低价格表，但对背后的执行机制感到困惑。pricing表有200种产品12年的价格数据，存在大量重叠的生效价格。单独执行CTE a 会生成数百万行，耗时极久；但完整执行整个CTE查询只需要约25秒，最终结果只有数千行。以下是近似的SQL代码：

with a as (
    select
         dt.[Date]
        ,p.product_no
        ,[date_start] = cast( RIGHT( MIN( RIGHT( '000000' + cast( p.price as varchar ), 6) + convert( varchar, p.date_start, 112 ) ), 8 ) as date )
        ,[date_expire] = cast( RIGHT( MIN( RIGHT( '000000' + cast( p.price as varchar ), 6) + convert( varchar, isnull(p.date_expire,'1/1/9999'), 112 ) ), 8 ) as date )
        ,[price] = MIN(p.price)
    from pricing p with (nolock) 
        inner join datetable dt with (nolock) on dt.[Date] between p.date_start and isnull(p.date_expire,getDate())
    group by 
         dt.[Date]
        ,p.product_no
), b as (
    select distinct
         a.date
        ,a.product_no
        ,[date_start] = iif( isnull(LAG(a.price) OVER (PARTITION BY a.product_no ORDER BY a.date),-1) <> a.price, a.date, null )
        ,[date_expire] = iif( isnull(LEAD(a.price) OVER (PARTITION BY a.product_no ORDER BY a.date),-1) <> a.price, a.date, null )
        ,a.price
    from a
)
select distinct
     b.product_no
    ,[date_start] = coalesce( b.date_start, LAG(b.date_start) OVER (PARTITION BY b.product_no ORDER BY b.date) )
    ,[date_expire] = coalesce( b.date_expire, nullif(LEAD(b.date_expire) OVER (PARTITION BY b.product_no ORDER BY b.date), cast( getDate() as date ) ) )
    ,b.price
from b
where b.date_start is not null
   or ( b.date_expire is not null 
        and b.date_expire <> cast( getDate() as date ) )
order by 1,2;

SQL Server的查询优化器不会按CTE的定义顺序逐步骤执行，而是把整个查询（包括所有CTE和最终查询）当作一个整体来分析，生成最优的执行计划。

具体到你的场景：

1. 谓词下推与计划合并：最终查询里的过滤条件（比如where b.date_start is not null）和后续的窗口函数逻辑，会被优化器“推”到前面的步骤中。优化器知道不需要生成CTE a 的所有百万行数据，只需要保留那些对最终结果有贡献的行，避免了不必要的数据生成和存储。
2. 避免中间结果物化：CTE本质是“逻辑视图”，默认不会把中间结果持久化到磁盘或内存（除非优化器判断物化更高效）。单独执行CTE a 时，数据库必须生成所有分组后的行；但完整查询时，优化器会把CTE a、b 和最终查询的逻辑整合，直接计算出最终需要的数千行结果，跳过了生成中间百万行的步骤。
3. 另外提一句你代码里的MIN技巧：把价格补成6位字符串（保证价格按数值大小排序），拼接上日期的112格式字符串，取MIN的时候会优先选价格最低的那条记录，再提取对应的日期，这个写法能在分组时同时拿到最低价格和对应的生效/过期日期，是个巧妙的实现。

CTE适合用在这些情况： #

• 逻辑分层的一次性查询：把复杂查询拆分成多个逻辑块，代码更易读，优化器能更好地整合整个查询逻辑，避免不必要的中间存储。
• 递归查询：CTE支持递归语法（WITH ... AS (...) UNION ALL ...），这是临时表做不到的，比如遍历树形结构数据。
• 不需要复用中间结果：如果中间逻辑只需要用一次，CTE的轻量性能更有优势。

临时表适合用在这些情况： #

• 需要复用中间结果：如果同一个中间数据集要在多个查询步骤里用到，临时表只需要计算一次，避免重复运算。
• 中间结果需优化：当中间结果很大，或者需要给中间结果创建索引来加速后续查询时，临时表可以显式创建索引，提升性能。
• 调试复杂查询：分步执行临时表，查看中间结果，更容易排查问题。
• 优化器失效时：当查询过于复杂，优化器生成的执行计划不够高效时，用临时表强制分步执行，避免优化器做出糟糕的决策。

内容的提问来源于stack exchange，提问作者volfied