在EXEC内部声明变量是否能优化SQL查询计划？

你当前调整的写法不存在实质性的正向收益，反而可能有额外的开销和执行计划风险，具体分析如下：

• 本质还是动态SQL拼接：两种写法都是把外部变量的值直接拼到EXEC执行的SQL字符串中，没有解决SQL注入的风险，只要传入的参数可控性不足，就存在被注入攻击的隐患。
• 性能层面无优势：第一种写法拼出来的查询语句是直接带字面量的WHERE Id = 123，优化器可以基于具体的字面量生成更贴合数据分布的执行计划；第二种写法在动态SQL内部声明局部变量再赋值，优化器在生成查询计划时无法感知变量的实际值，会采用默认的基数估算规则，反而容易生成不合适的执行计划，导致查询性能下降，同时内部变量声明和赋值还会增加微不足道的额外执行开销。
• 计划缓存层面没有改善：两种写法每次传入不同的参数值时，生成的动态SQL文本都不一样，都会触发新的执行计划编译，无法复用计划缓存，会导致计划缓存膨胀，白白占用数据库内存。

正确的优化方案 #

应该使用sp_executesql实现参数化动态SQL，这才是兼具安全性和性能收益的写法，示例代码如下：

DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456

DECLARE @Sql NVARCHAR(MAX) = N'
SELECT * FROM Employees WHERE Id = @InnerEmployeeId
SELECT * FROM Customers WHERE Id = @InnerCustomerId
'

EXEC sp_executesql @Sql,
    N'@InnerEmployeeId int, @InnerCustomerId int',
    @InnerEmployeeId = @EmployeeId,
    @InnerCustomerId = @CustomerId

该写法的核心收益：

• 完全避免SQL注入风险，参数值不会被当成SQL语句的一部分解析
• 无论传入的参数值怎么变，执行的SQL文本都是固定的，执行计划可以复用，大幅减少编译开销和计划缓存占用
• 绝大多数场景下优化器可以基于实际参数值生成更适配的执行计划，参数嗅探的特殊问题可以通过其他手段单独调优，整体利远大于弊

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