Should local variables for constants be avoided in stored procedures?

Question

When I write SQL I try to make it as readable as possible. Among other things I often declare "constants" instead of using "magic numbers".

i.e. instead of

WHERE [Order].OrderType = 3

I do

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

This works fine and I have not noticed any performance issues for the size of queries and data I normally work with.

Recently I read an article about parameter sniffing and workarounds (https://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/). In the artice one of the workarounds presented is "use local variables".

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.

This makes me a bit worried since my interpretations is that I might get a suboptimal plan in my stored procedures just because I use a local variable instead of a "magic number".

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

Can someone clear this up for me?

• Is there a difference between using a "magic number" and a local variable?
• 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?

Answer 1

As documented in the article 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

Regarding your questions...

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

No, never, it can auto parameterise adhoc queries but parameters behave differently from variables and can be sniffed. By default it will only do this in very limited circumstances where it is "safe" and unlikely to introduce parameter sniffing issues.

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

Yes, the statement is generally compiled before the variable value is even assigned. And even if the statement was to be subject to deferred compilation (or happen to be recompiled after the assignment) values of variables are still never sniffed except if you use option (recompile). If you use the literal inline SQL Server can look up that literal value in the histogram and potentially get much more accurate estimates rather than resorting to guesses. Accurate row estimates are important in getting the correct overall plan shape (e.g. Join type or access method selection) as well as getting an appropriate memory grant for your query.

The book "SQL Server 2005 practical troubleshooting" has this to say on the issue.

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

(NB: this has not in fact been enabled in any version to date)

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

This applies to every use of variables. Parameters can be sniffed though so if you were to have a variable in the outer scope passed as a parameter in the inner scope that would allow the variable value to be sniffed.

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

If the plan is going to be sensitive to the exact variable value than yes. There are certain places where it will be entirely innocuous however.

The disadvantage of option (recompile) as a fix is that it recompiles the statement every time. This is unnecessary when the only reason for doing so is to get it to sniff a variable whose value is constant. The disadvantage of option (optimize for) with a specific literal value is that if the value changes you need to update all those references too.

Another approach would be to create a view of Constants.

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

Then, instead of using a variable at all for those, reference that instead.

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

This will allow the value to be resolved at compile time and only need to be updated in one place.

Alternatively, if you use SSDT and database projects you could use a sqlcmd variable that is defined once and assigned to and then replace all your TSQL variable references with that. The code deployed to the server will still have "magic numbers" but in your source code it is a single SqlCmd variable (NB: For this pattern you might need to create a stub procedure in the project and use the post deployment script to actually alter it with the desired definition and performing the sqlcmd substitutions).