More on magic numbers

Posted by Chris on October 04, 2006

I recently posted a recommendation about “avoiding magic numbers in stored procedures”:/articles/2006/01/12/stored-procedure-guidelines-avoid-magic-numbers (and other t-sql routines of course). Yesterday I updated that post with some important info that invalidates the recommendation from a performance view. Please read the updated post if you have not seen this. However, I still think the advice of avoiding magic numbers to make code more readable is important.

So what can we do to write readable code and still have good performance? Well, the simple answer is unfortunately that we cannot in this case. In SQL Server 2005 we an use the new query hint OPTIMIZE FOR to allow us to use a variable instead of a literal in our code, but I am not sure I would say the code below is more readable than simply using a literal:

DECLARE @StartOrderDate datetime
SET @StartOrderDate = ’20040731′

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate
OPTION (OPTIMIZE FOR ( @StartOrderDate = ’20040731′ ) )

This query will produce the same plan as if we just used the literal instead of a variable. If the literal represents a value that is not at all easily understood (unlike the date literal above) then maybe we could say that this code is more easily readable than the alternative, but for most cases it will just look silly.

However, when thinking about this I came up with another idea. What if we could use named (declared) literals in our code, just like constants in a language such as C#? We could declare a constant with a read-only literal, and then use that constant in the query. The effect would be the same as using the literal by itself. Some examples of how this could look is shown below:

DECLARE @SomeConstant int = 42
DECLARE !SomeConstant int = 42
CONSTANT @SomeConstant int = 42
READONLY @SomeConstant int = 42

So what do you think? Am I just too concerned with making stored procedures readable, or is this something you would like to see in a future version of SQL Server? How should it look and work?


Trackbacks are closed.

blog comments powered by Disqus