SQL Server 普通CTE查询是否会自动优化下推外层过滤条件？

问题解答 #

SQL Server的查询优化器会自动优化该CTE查询，不会先加载My_Table的全部记录再做过滤。

具体原理

• SQL Server中的普通非递归CTE本质是临时定义的逻辑查询别名，没有预先固化的结果集，执行时会被优化器展开到外层查询中统一生成执行计划。
• 你给出的示例属于无特殊逻辑的简单CTE，优化器会自动做谓词下推优化，将外层的WHERE col1 > 10过滤条件、TOP(10)限制、ORDER BY col2排序逻辑直接下推到基表My_Table的访问步骤中，实际执行逻辑等价于直接对基表做查询：

SELECT TOP(10)
    col1,
    col2
FROM
    My_Table
WHERE
    col1 > 10
ORDER BY
    col2

• 只有当CTE内部包含TOP、DISTINCT、聚合计算、递归逻辑等特殊操作时，谓词下推才可能受限制，才会出现先计算CTE结果再做外层处理的情况。
• 你可以在SSMS中打开「包括实际的执行计划」功能运行查询，直接查看执行步骤验证优化效果，基表扫描节点会直接携带col1>10的过滤条件，不会出现全表加载的操作。

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