0

Do newer versions of SQL Server (after SQL Server 2017) optimize the queries in the stored procedures that use variables instead of the so called "magic numbers"? E.g. Instead of using

SELECT ProductName, ProductDescription from Products 
where ProductCategory in (2, 5)

can we use

DECLARE @GADGETS_CATEGORY INT = 1
DECLARE @CLOTHES_CATEGORY INT = 5

SELECT ProductName, ProductDescription from Products 
where ProductCategory in (@GADGETS_CATEGORY , @CLOTHES_CATEGORY)

and still benefit from the same query optimizations?

I know there are some older questions on the topic like this one, but it would be nice to know if this has been improved.

And yes, I know that you can create a table with categories, make a join with that table and than use strings in the WHERE clause but that is not the point. Magic numbers should not exist in stored procedures. For example you should be able to write this and SQL server should be able to replace the inline constant in queries when it compiles the SP:

DECLARE @DEFINED_LIMIT_IN_MB SMALLINT = 5;
SELECT ImageName, ImageDescription from Images where ImageSize < @DEFINED_LIMIT_IN_MB
1

2 Answers 2

1

For example you should be able to write this and SQL server should be able to replace the inline constant in queries when it compiles the SP:

DECLARE @DEFINED_LIMIT_IN_MB SMALLINT = 5;
SELECT ImageName, ImageDescription from Images where ImageSize < @DEFINED_LIMIT_IN_MB

So one problem I imagine with variables being different than literals (aka constants), as the name implies, is there's a chance for the value to vary. So there is no guarantee that the value you give the variable at declaration is going to stay the same at runtime up until the part where it's used in a predicate.

So it's not as simple as you may initially assume, even though in your specific example it would be, but your example is only one case of many for how variables can be used in code. Literals only have a single use case, the exact value they are defined as only for the exact place they are being used in the code.

For the SQL Engine to be able to do what you wanted with variables, it would need to wait until runtime when the variable is evaluated just before its use in a predicate, to then be able to know what the value really is. But the way the SQL Engine works, is it builds the query plan first, and then it executes said query plan. Evaluating the value of a parameter is something that can only happen during execution, but at that point the query plan is already built, it can't be dynamically changed in the middle of execution.

As mentioned in the answer you linked to, one can use the OPTION (RECOMPILE) hint on the queries that use the variable, to cause it to be sniffed at runtime. This causes that query to not store its execution plan in the plan cache, and instead generate a new one based on the value of the variable at runtime. There is some overhead for using that hint, but generally it's ok if your query isn't running super frequently (e.g. 100+ times a second).

3
  • Yes, but I don't want it to be recompiled. I want it to be cached. And I would like the SQL Server compiler to analyze the code, see that the variable is not modified before its usage (it behaves like a constant) and compile the execution plan like it would have been used inline. In C# and many programming languages the constants are replaced inline at compile time. I guess what I want is something the const keyword is C#. Jun 2 at 13:21
  • @CristianRusanu I understand, but procedural languages like C# have it easier, because they don't need to translate the code you write into an optimal execution plan based on data factors. C# instead just literally executes what you tell it. Database languages and engines need to come up with the how to execute themselves, and develop an execution plan that can't be modified at runtime. But variables can be changed at runtime, making it essentially not possible to do what you're asking. Yes, a const keyword would solve this problem though, since it guarantees the object can't change.
    – J.D.
    Jun 2 at 16:06
  • @CristianRusanu But I also don't think constants are needed as much in SQL, especially since the length of code in a procedure should be short enough to be manageable, and because their are equivalent solutions already, as you've already mentioned, such as making the data table driven - which is more fitting in the relational layer.
    – J.D.
    Jun 2 at 16:08
2

If you want it to "sniff" the values of the variables and produce the same plan as

ProductCategory in (1, 5)

would then you need to add an OPTION (RECOMPILE) - but then you pay the costs for recompiling on every execution.

Generally SQL Statements are compiled in a batch so the statements are compiled before any assignment to the variables is done.

Even if the statement is recompiled or subject to deferred compile though (and hence the variable assignment has already happened) it still won't sniff the variable value without the OPTION RECOMPILE hint as assigning to variables was a common technique to avoid parameter sniffing issues and that would defeat this.

If you want to define these constants in one place and have them inlined and the values visible to the query optimiser you could consider putting them in a view, inline table valued function or inline scalar UDF.

TBH all of these are a bit clunky but there is no such thing as a variable that gets inlined as you want.

If you use SSDT then one other clunky option would be to define SQL CMD variables

enter image description here

Your procedures can then reference them as '$(CLOTHES_CATEGORY)' etc and when published it will get replaced with '5'. But this is now a string literal rather than an integer literal so you would need to validate that this in itself does not cause any issues. For comparison against an integer column though I would expect the implicit cast to int to be resolved at compile time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.