Stored Procedure Guidelines: Avoid magic numbers

Posted by Chris on October 04, 2006

*UPDATE*: I have just read a post by “Ian Jose”:http://blogs.msdn.com/ianjo/default.aspx from the “Query Optimizer team”:http://blogs.msdn.com/sqlqueryprocessing/default.aspx that sort of invalidates this recommendation. He writes that you should “use literals, or at least unchanged parameters, for query inputs”:http://blogs.msdn.com/ianjo/archive/2005/11/10/491538.aspx. As his post shows, using variables as I write below can often have a negative impact on performance. I think this is a real shame, since using magic numbers in this way definitely makes the code less readable. Possibly the best way to go is to move the query to a separate procedure and pass the magic number from the ‘outer’ procedure as a parameter to this new procedure, like Ian describes in his post. That way we would avoid magic numbers and still get the best plan, although I am not sure the readability is helped by this added complexity. I will leave my post as it was originally written because I think it is still good advice to think about the readability of code. The part about watching out for non-SARGable where clauses is also still valid, which Ian hints about in “another post”:http://blogs.msdn.com/ianjo/archive/2005/11/10/491541.aspx.

Most programmers are aware of the pitfalls involved with using magic numbers in code (at least I hope so). For instance, instead of writing something like @if(foo == 42)@ you should create a constant and use @if(foo == MeaningOfLife)@, since not everyone knows that 42 equals the meaning of life (you might think that all computer geeks know this, but they actually do not). For some reason though the good practice of not using magic numbers seems to be forgotten when it comes to writing stored procedures, or just sql scripts in general.

In stored procedures you often see something like @WHERE somecolumn = 42@. There’s a magic number right there. In a situation where somecolumn should be compared to a parameter that was passed to the procedure it would instead be @WHERE somecolumn = @someparameter@. My advice is that the same style should be used even when the number is not a parameter but just a hard-coded value. So:

*Avoid using magic numbers in SQL statements. Declare them as variables and use the variable instead.*


– Bad code, do not use this style
SELECT …
WHERE somecolumn = 10

– Good code, use this style
DECLARE @somevariable SMALLINT
SET @somevariable = 10

SELECT …
WHERE somecolumn = @somevariable

Apart from making the code more readable, this advice can also be important for performance reasons. The literal 10 used in the example above is seen by SQL Server as an integer constant. This means that if the data type of somecolumn is not integer then an implicit cast needs to be done to be able to compare the values. Now, consider what happens when somecolumn has the data type smallint. The data type precedence rules for SQL Server state that a smallint is converted to an integer. So, the where clause in reality becomes @CAST(somecolumn AS INT) = 10@. Not only are we wasting CPU cycles casting the value of somecolumn for every row, even more worse is that this clause does not confirm to the SARG rules. These state that to be able to use an index seek for evaluating a where clause it needs to follow the formula @column operator expression@. With the CAST function the clause becomes @expression operator expression@, so a table (or index) scan is used.

This problem is of course easily avoided by casting the integer constant to a smallint (@somecolumn = CAST(10 AS SMALLINT)@), but then we would still have the magic number. And it is easy to forget to cast the literal and forget about the implicit cast. Avoiding magic numbers force us to think about what data type we actually want.

Trackbacks

Trackbacks are closed.

blog comments powered by Disqus