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:
|
1 |
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:
|
1 |
SET @NextInterval = @CurrentInterval * 1440; -- minutes per day |
Or perhaps better to say
|
1 |
SET @NextInterval = @CurrentInterval * 24 * 60; -- minutes per day |
Now we can imitate what's needed by using variables:
|
1 2 3 4 |
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:
|
1 |
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.
|
1 |
DECLARE @HOURS_PER_DAY CONSTANT int = 24; |
I noticed there was an existing suggestion for this in UserVoice. Please vote for it here.
Sir, this is a dead end. I admire your optimism, however T-SQL is dead. Microsoft is not making any effort to improve developer's experience and efficiency of programming in SQL Server. New features in T-SQL are laughable. The reason is this product lacks trendy "Azure" moniker. The gap between T-SQL and e.g. PostgreSQL variant of SQL is astounding. T-SQL lacks so much. Instead they're adding features predenting that SQL Server belongs to family of products it really doesn't. Heck, even SQLite has better SQL than T-SQL.
I've waited 20 years for proper regular expression search/replace/extract – no luck. They've been standardized in SQL:2011 with "SIMILAR TO" ans special form of "SUBSTRING" operators. In T-SQL – nothing at all. I understand that people from the product team may not know regexes nor use them, but they're not developing the product for themselves. Now they're introducing more and more external languages to the product with minimal applicability. Addition of Java is a joke. What percentage of users is going to use it? What's the performance? I have seen some benchmarks and this is a crap.
And what about using UNLOGGED/NOLOGGING tables? Everybody would praise that, but no, it's better to add Java. For God's sake.
Materialized CTEs? Nope. It's better to add graphs like it's crippled brother of Neo4j.
IS DISTINCT FROM operator (implemented internally) maybe? No! Go complicate your comparisons, we're making good laugh from it. IS NOT TRUE, IS NOT FALSE? Hell, no!
WINDOW clause in queries? No, repeat the same long window definitions over and over.
INTERSECT ALL/EXCEPT ALL? No way.
FILTER clause for aggregates? Nope.
NULLS FIRST/LAST maybe? No, write your stupid ORDER BY CASE.
UNNEST? No.
I continue for hours.
Reading text files is a joke. Not even a beta quality. Not to mention writing text files.
I was dreaming there will be one release in which Microsoft will implement a lot of standard SQL features and maybe some nice additions. This is not going to happen. They don't care about devs at all. T-SQL improvements doesn't look good on slides. Adding Java does.
So in the end, I admire your passion Sir, but sadly it's not going to happen.
I hear and understand your thoughts, and share the frustration. Currently I see a little glimmer of hope in the product group. So perhaps, you never know, we might get a few things to happen.
TOTALLY AGREE WITH YOU.
You could add a sequence object where min/max value is 1440 and cycle.
Hi Peter, yes, that would be an option for numbers, but not for strings.