SQL Server优化器如何处理Scalar UDF与Co-related Subqueries

两种写法的后台处理逻辑 #

• 关联子查询（Correlated Subqueries）写法
  SQL Server查询优化器会先识别出这是单行返回的关联子查询，绝大多数场景下会直接将其重写为等价的LEFT JOIN逻辑，不会真的逐行循环查询table2。整个查询会作为一个整体做成本评估，优化器会根据表数据量、索引分布自动选择嵌套循环、合并连接、哈希连接中成本最低的执行方案，执行效率较高。
• 标量UDF写法
  默认情况下SQL Server会对标量UDF做逐行调用处理：首先读取table1的所有符合条件的行，对每一行的col1值单独触发一次UDF调用，每次调用都需要单独做上下文切换、单独查询table2。同时标量UDF默认会阻止并行执行计划生成，哪怕数据量很大也只能串行执行。即便是SQL Server 2019及以上版本的标量UDF内联功能，也仅对符合特定规则的简单UDF生效，复杂UDF仍然会走逐行调用逻辑，性能远低于关联子查询。

更优的实现写法 #

1. 首选LEFT JOIN写法（全版本兼容，优化空间最大） #

和原有两种写法的返回结果完全一致，优化器可以完全自主选择最优执行计划：

SELECT 
    t1.col1,
    t1.col2,
    t2.col3
FROM table1 t1
LEFT JOIN table2 t2 
    ON t1.col1 = t2.col1

如果业务要求仅返回table2有匹配的行，把LEFT JOIN替换为INNER JOIN即可。

2. 复杂匹配场景用APPLY运算符（SQL Server 2005及以上支持） #

如果后续需要加排序、TOP等更复杂的单行匹配逻辑，用OUTER APPLY更灵活：

SELECT 
    t1.col1,
    t1.col2,
    t.col3
FROM table1 t1
OUTER APPLY (
    SELECT TOP(1) col3 
    FROM table2 t2 
    WHERE t2.col1 = t1.col1
    -- 可自定义排序规则取指定行
) t

配套优化建议 #

• 如果table2的col1是唯一值，直接给col1加主键/唯一约束，可进一步降低执行计划成本
• 若col1非唯一但业务保证单值，可建覆盖索引避免键查找：CREATE NONCLUSTERED INDEX IX_table2_col1 ON table2(col1) INCLUDE (col3)
• 非必要不使用标量UDF做逐行匹配逻辑，相同逻辑下性能通常比JOIN写法低10倍以上，数据量越大性能差距越明显。

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