SQL: The need for constants in T-SQL

If you look at the code in the image above, I have one immediate reaction.

What are those numbers that are sprinkled throughout the code?

I see this all the time in T-SQL code. Yet in any programming language, it’s a really poor idea to do this. It leads to very fragile code that’s so hard to maintain longer-term.

So why do people do it?

So, you might wonder why people do this in T-SQL. Couldn’t they just do this at the start of the code?

DECLARE @TARGET_LOCALE_ID int = 12;
DECLARE @APPLICATION_LANGUAGE_TYPE = 4;

Then they could use the values within the T-SQL code:

SELECT OS_Family, LocaleID, LanguageName 
FROM SDU_Tools.OperatingSystemLocales
WHERE LocaleID <> @TARGET_LOCALE_ID
AND LanguageType = @APPLICATION_LANGUAGE_TYPE;

But the problem with this is that it’s not the same. In simple queries, SQL Server is able to replace one with the other as part of a constant scan while optimizing the code. But unfortunately this doesn’t always play out as you’d expect.

What’s needed

What is really needed is the ability to declare constant values. TARGET_LOCALE_ID is far more meaningful than 12.

For example, in PostgreSQL, you can do this:

DECLARE TARGET_LOCALE_ID CONSTANT int = 12;

Mind you, most people writing PostgreSQL code would have that name in lower-case, but I like constants to be really obvious.

Assuming I don’t want to break T-SQL too much, I’d like to see T-SQL have an option like this:

DECLARE @TARGET_LOCALE_ID CONSTANT int = 12;

But ideally, I wouldn’t need the @ prefix.

Even better: Some global scope options

The options I mentioned before were at the session level. In addition to that, I think SQL Server would really benefit from the ability to declare constants at a global scope for each database, and also for each server.

These could be handled like database properties and/or server properties. They could be very powerful indeed.

I should mention that much of this could be done with SQLCLR objects but because they aren’t supported everywhere, I’ve discounted that option. We still really need constants.

2025-08-14