{"id":111697,"date":"2026-07-08T12:00:00","date_gmt":"2026-07-08T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=111697"},"modified":"2026-07-08T12:27:20","modified_gmt":"2026-07-08T12:27:20","slug":"whats-missing-in-t-sql-heres-my-t-sql-wish-list-of-features-that-developers-actually-need-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/whats-missing-in-t-sql-heres-my-t-sql-wish-list-of-features-that-developers-actually-need-in-sql-server\/","title":{"rendered":"What’s missing in T-SQL? My wish list of features that developers actually need in SQL Server"},"content":{"rendered":"\n

Edward Pollack recently wrote an excellent wish list<\/a> for T-SQL features he’d like to see in an upcoming version of SQL Server. Now, it’s my turn. This article covers what I’d like to see the most<\/strong>.<\/p>\n\n\n\n

Extensibility added to SQL Server<\/h2>\n\n\n\n

I’ve had this wish for as long as I’ve worked with SQL Server<\/a>, and I started with it in 1992! I can’t tell you how many times I’ve been in meetings with the product group, they show us a new feature, and I ask how I can build things that would enhance or complete what they’ve done. The response has almost always been “this is version 1; hopefully we’ll get to extensibility<\/strong> in version 2 or later”.<\/p>\n\n\n\n

And, put simply, they never get there.<\/p>\n\n\n\n

The problem I see with this, is that it often undermines – or worse, wastes<\/em> – the product group’s own work. If they ship a feature and it’s not complete, there’s no way for us to fill in enough of what’s missing to make it useful. Then, when the feature doesn’t get enough traction in the market, it often just falls by the wayside. There are endless examples of this, but let me pick just one. <\/p>\n\n\n\n

Back in SQL Server 2005, Microsoft added the ability to create our own data types as part of SQL CLR<\/a>. It was great to see some extensibility appearing but, as soon as I looked into it, I realized I couldn’t use it to create what I wanted as there was no way to appropriately index the data type. I could only index properties of the data type via computed columns<\/a> – not the data type itself.<\/p>\n\n\n\n

This is because, with new data types, you often need a different type of index<\/a>. So, I kept asking for the ability to create a custom index type, but the response was always along the lines of, “we don’t really need a new index type.”<\/em><\/p>\n\n\n\n

But clearly we did<\/em> need a new index type…<\/h3>\n\n\n\n

…and this has been proven again and again<\/em> over the years. With the XML data type<\/a>, Microsoft added two new index types. In SQL Server 2008, when they added their own new SQL CLR-based data types<\/a> for geometry and geography, they added a new spatial index<\/a>. For me, this should have been just one instance of a new custom index type. <\/p>\n\n\n\n

It was no surprise, then, to see a new index type created when JSON<\/a> was later added. When the vector data type<\/a> was introduced, so was a new type of index for it. Since I now work with Azure SQL Database<\/a> so much, and Microsoft removed their initial support for SQL CLR, we try to avoid using it in any of our SQL Server work. And we’re back to having no meaningful way to extend the language. <\/p>\n\n\n\n

Meanwhile, with my other work in PostgreSQL<\/a>, it’s something I really appreciate – and it’s part of the reason why the platform is considered more “developer-friendly” by many people. I’d like to see the SQL Server team think more about building an ecosystem rather than just shipping a product where they feel they need to supply everything<\/em>.<\/p>\n\n\n\n

Constants in SQL Server<\/h2>\n\n\n\n

I review large amounts of T-SQL<\/a> and I so often see hard-coded values throughout the code. Performance-wise, it’s totally fine to write code like:<\/p>\n\n\n\n

WHERE AdditionalStatus = 146<\/code><\/p>\n\n\n\n

But as for code quality? What on earth is that ‘146’? Even if there is a comment there to explain it, it’s not needed.<\/p>\n\n\n\n

Instead, we can put values like this in a variable, perhaps at the top of our code:<\/p>\n\n\n\n

DECLARE @EXTENDED_STAY_STATUS int = 146;<\/code><\/p>\n\n\n\n

and use it in code:<\/p>\n\n\n\n

WHERE AdditionalStatus = @EXTENDED_STAY_STATUS<\/code><\/p>\n\n\n\n

Unfortunately, though, it might not execute the same. You see this all the time when someone is trying to debug stored procedure<\/a> code – they replace the parameters at the top with variables and then find it doesn’t run the same.<\/p>\n\n\n\n

If we had true<\/em> constants, the optimizer could know about them, and use them like literal values:<\/p>\n\n\n\n

DECLARE @EXTENDED_STAY_STATUS CONSTANT int = 146;<\/code><\/p>\n\n\n\n

I’d like to go even further and have the ability to declare constants at the database level – and potentially at the server level, too (not just session level.)<\/p>\n\n\n\n

\n
\n
\n
\n

Enjoying this article? Subscribe to the Simple Talk newsletter<\/h3>\n
\n Get selected articles, event information, podcasts and other industry content delivered straight to your inbox. <\/div>\n <\/div>\n Subscribe now<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

Consistency in SQL Server<\/h2>\n\n\n\n

Given the requirement for backward compatibility, this one is hard…but I really<\/em> wish there was more consistency in how features and language elements are designed for SQL Server. We have names like DATETIMEOFFSETFROMPARTS<\/code>, for example, but also have DENSE_RANK<\/code>, COUNT_BIG<\/code>, etc. There never seems to be any rationale for when names have spaces separating or are just run together.<\/p>\n\n\n\n

The bigger issue, though, is the logical inconsistency. Even in SQL Server 2025, functions were added for EDIT_DISTANCE()<\/code> and JARO_WINKLER_SIMILARITY()<\/a><\/code>. What’s wrong with that, you might ask? Well, one of them – EDIT_DISTANCE<\/code> – is named after what it’s measuring. And the other one – JARO_WINKLER_SIMILARITY<\/code> – is named after the algorithm used! <\/p>\n\n\n\n

You’d hope the naming would follow the same pattern: either named after the effect, or after the algorithm. I’d expect that across versions, but even more so within a single version of the product. EDIT_DISTANCE()<\/code> should probably have been called LEVENSHTEIN_DISTANCE()<\/code> for the Levenshtein<\/a> (or Damerau-Levenshtein) algorithm.<\/p>\n\n\n\n

I’d also like to see consistency regarding abbreviations. For example, I wish EOMONTH()<\/code> was END_OF_MONTH()<\/code>. Did we really need to save 3 characters in the same version as other functions with really long names were shipped? I’m told it was named after an old Excel function, but is that really<\/em> how we want new language features designed? <\/p>\n\n\n\n

Also, while we’re at it – why is there no START_OF_MONTH()<\/code>?! And we had tinyint<\/code>, smallint<\/code>, int<\/code>, and bigint<\/code>, but then we got smalldatetime<\/code>, datetime<\/code>, and datetime2<\/code>.<\/p>\n\n\n\n

Then there’s SYSDATETIME()<\/code>, SYSDATETIMEOFFSET()<\/code>, and SYSUTCDATETIME()<\/code>. You would have guessed the new option for getting just the date would be SYSDATE()<\/code> right? No! It’s CURRENT_DATE<\/code>. We still don’t have SYSUTCDATE()<\/code>, either.<\/p>\n\n\n\n

We don’t need to confuse users like this.<\/p>\n\n\n\n

Enumerations in SQL Server<\/h2>\n\n\n\n

Enumerations<\/a> are a static ordered set of values. For example, I might have the need to store a column called NextAction<\/em>, with the allowable values being:<\/p>\n\n\n

\n