sql-server - 在存储过程中是否应该避免常量的局部变量？

Question

当我编写 SQL 时，我会尽量使其可读。除此之外，我经常声明“常量”而不是使用“魔数(Magic Number)”。

即而不是

WHERE [Order].OrderType = 3

我愿意

DECLARE @OrderType_Cash AS int = 3;
...
WHERE [Order].OrderType = @OrderType_Cash

这工作得很好，我没有注意到我通常使用的查询和数据的大小有任何性能问题。

最近我读了一篇关于参数嗅探和解决方法的文章( https://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/ )。本文中提出的解决方法之一是“使用局部变量”。

4. Workaround : Use local variable – This workaround is very similar with previous one (OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))) – when you assign the paramaters to local ones SQL Server uses statistic densities instead of statistic histograms – So It estimates the same number of records for all paramaters – The disadvantage is that some queries will use suboptimal plans because densities are not precise enough as the statistic histogram.

这让我有点担心，因为我的解释是，仅仅因为我使用局部变量而不是“魔数(Magic Number)”，我可能会在存储过程中得到次优计划。

我还认为 SQL Server 自动将“魔数(Magic Number)”转换为变量以便重用计划。

有人可以帮我解决这个问题吗？

• 使用“魔数(Magic Number)”和局部变量有区别吗？
• 如果是，它仅适用于存储过程还是也适用于即席查询和动态 SQL？
• 像我一样使用局部变量是一个坏习惯吗？

Answer 1

如文章 Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 中所述

If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables

关于您的问题...

I was also under the impression that SqlServer automatically convert "magic numbers" into variables in order to reuse plans.

不，永远不会，它可以 auto parameterise即席查询，但参数的行为与变量不同，并且可以被嗅探。默认情况下，它只会在“安全”且不太可能引入参数嗅探问题的非常有限的情况下执行此操作。

Is there a difference between using a "magic number" and a local variable?

是的，该语句通常是在分配变量值之前编译的。即使该语句要进行延迟编译(或者在赋值后碰巧重新编译)，变量的值仍然不会被嗅探，除非您使用 option (recompile) 。如果您使用文字内联，SQL Server 可以在直方图中查找该文字值，并有可能获得更准确的估计，而不是依靠猜测。准确的行估计对于获得正确的总体计划形状(例如联接类型或访问方法选择)以及为查询获得适当的内存授予非常重要。

《SQL Server 2005实用故障排除》一书对此问题有这样的说法。

In SQL Server 2005, statement level compilation allows for compilation of an individual statement in a stored procedure to be deferred until just before the first execution of the query. By then the local variable's value would be known. Theoretically SQL Server could take advantage of this to sniff local variable values in the same way that it sniffs parameters. However because it was common to use local variables to defeat parameter sniffing in SQL Server 7.0 and SQL Server 2000+, sniffing of local variables was not enabled in SQL Server 2005. It may be enabled in a future SQL Server release though

(注意:迄今为止，实际上尚未在任何版本中启用此功能)

If yes, is it only in stored procedures or does it also apply to ad-hoc queries and dynamic sql?

这适用于变量的每次使用。不过，参数可以被嗅探，因此如果您要将外部作用域中的变量作为内部作用域中的参数传递，则允许嗅探变量值。

Is it a bad habit to use local variables like I do?

如果计划对确切的变量值敏感，那么就选择"is"。然而，在某些地方它是完全无害的。

option (recompile)的缺点解决方法是它每次都会重新编译该语句。当这样做的唯一原因是让它嗅探值恒定的变量时，这是不必要的。 option (optimize for)的缺点具有特定文字值的是，如果该值发生变化，您也需要更新所有这些引用。

另一种方法是创建常量 View 。

CREATE VIEW MyConstants
AS
SELECT 3 AS OrderTypeCash, 4 AS OrderTypeCard

然后，不要使用变量，而是引用它。

WHERE [Order].OrderType = (SELECT OrderTypeCash FROM MyConstants)

这将允许在编译时解析该值，并且只需要在一处更新。

或者，如果您使用 SSDT 和数据库项目，您可以使用定义一次并分配给的 sqlcmd 变量，然后用它替换所有 TSQL 变量引用。部署到服务器的代码仍将具有“魔数(Magic Number)”，但在源代码中它是单个 SqlCmd 变量(注意:对于此模式，您可能需要在项目中创建一个 stub 过程，并使用部署后脚本来实际更改它具有所需的定义并执行 sqlcmd 替换)。