SQL: T-SQL really needs Constants

In “Progamming 101”, developers learn that sprinkling hard-coded numbers and strings throughout their code, is a really lousy idea. Yet I regularly see T-SQL code that’s littered with hard-coded numbers and “special” string values. I really wish we could avoid that. It would greatly improve code quality, and improve debugging for procedures.

Take the following code as a really simple example:

SET @NextInterval = @CurrentInterval * 1440;

It’s not at all obvious why the value 1440 is there. In this case, it was the number of minutes in a day. Today, we can do this:

SET @NextInterval = @CurrentInterval * 1440; -- minutes per day

Or perhaps better to say

SET @NextInterval = @CurrentInterval * 24 * 60; -- minutes per day

Now we can imitate what’s needed by using variables:

DECLARE @HOURS_PER_DAY int = 24;
DECLARE @MINUTES_PER_HOUR int = 60;
...
SET @NextInterval = @CurrentInterval * @HOURS_PER_DAY * @MINUTES_PER_HOUR;

And that’s sort of OK, but sadly while it’ll work in this simple example, generally in T-SQL if you went and replaced all your hard-coded values with variables, you’d mess up your query plans, big time. The problem is that at compile time, SQL Server doesn’t know the value.

That’s a common problem that I see when people are debugging stored procedures. They comment out the procedure header, and replace the parameters with variables, then try to debug the code. But this doesn’t work properly. When compiling a stored procedure, SQL Server “sniffs” the values being passed to the parameters and uses those to influence the plan.

Having true constants would avoid that. SQL Server could recognize and use the constant values during compilation.

Another simple example would be having values for TRUE and FALSE.

Existing Alternatives

There are some existing alternatives in T-SQL. You could create a SQLCLR data type called “Constant’ and then declare the values as properties. i.e. Constant.HoursPerDay but we’re increasingly using Azure SQL Database and sadly SQLCLR isn’t there. So that’s a non-starter.

You could put a CTE at the top of your query and define all the values there, then cross join it to the rest of the query, but that’s just ugly.

You could create a schema called Constant and define a set of functions in it, that returned the values: Constant.HoursPerDay() but that would likely still lead to poor performance.

Other Databases

PL/SQL supports this. See the documentation here.  The syntax is simple:

DECLARE hours_per_day CONSTANT int := 24;

Please Vote

It would seem a straightforward extension to T-SQL to allow the word CONSTANT to be added after the name of a variable.

DECLARE @HOURS_PER_DAY CONSTANT int = 24;

I noticed there was an existing suggestion for this in UserVoice. Please vote for it here.

2020-03-05