Anyone who’s spent years writing T-SQL has a mental list of “why doesn’t SQL Server just do this already?” moments. Here are 9 of those from Louis Davidson’s perspective – a wish list of practical, coding-oriented feature requests drawn from real-world pain points.

When I was asked about doing a post like this, I looked around at similar articles to make sure I wasn’t going to just repeat everyone’s list verbatim. I checked out Brent Ozar’s post (and definitely agreed that I would like to be able to write the FROM clause first), and Erland Sommarskog has a huge list – a few of which overlap with mine.

This backwards take on what Mike Walsh would like dropped from SQL Server, this 6-year-old Reddit thread that still has some valid points, as well as a hopefully humorous take here by Jeff Smith, are other samples of what I discovered during my search.

There’s also the recent wish lists from Ed Pollack (I disagree with arrays, but strongly agree with an OVERLAPS operator!) and Greg Low here on Simple Talk.

Finally, I stopped looking around when I found Kevin Chant’s T-SQL Tuesday post on the topic. It occurred to me that, aside from Erland’s post (and Aaron Bertrand’s list from early 2025), none of them have a lot in common with my desires for SQL Server. They are, as you will see, always coding-oriented.

So, here are 9 things I wish SQL Server had – and a couple of them are kind of, sort of, multiples. They are mostly unordered as to which I would beg for first, though I will admit that the first one listed here is something I have asked about for over 20 years. In fact, back in 2005, it was said they would look at it for the “next” version…!

System managed columns in SQL Server

One of the things I want in most every table I create are (what I call) “physical-only” columns. I want to set a value (which will be set when a row is created and/or updated) that doesn’t really matter to the user and should only ever be modified by the system. This might be the current user, application, time, an initial value from a different column, or whatever I need for a given situation.

For example, I would like to be able to implement a simple row_created_time and row_modified_time using syntax more or less like the following:

Now, whenever a row is added to TableX, both row_created_time and row_modified_time will be set to the current local time for the server. Whenever the row is updated, just the row_modified_time would be set.

In some ways, you might think this would be best done by giving us access to some metadata about the lifecycle of rows in our tables. That is true – however, I want complete control if possible. I want to be able to set the time, round it off, fill this with a random number, just add 1 to the previous value, etc.

Of course, all of this can be done with an INSTEAD OF trigger, but it’s a lot of work to keep INSTEAD OF triggers maintained as the structure of a table is changed over time.

Using an AFTER trigger, on the other hand, is less work, but has the downside that you must update the same row twice. That means – if you are using a feature like mentioned in the next section – more history recorded.

User-defined temporal tables in SQL Server

System versioned temporal tables have been a part of SQL Server since 2016 and are awesome to help you see what has changed in your data over time. My coworkers and I love them. You can just ask “when the heck did this change, and what was the previous value?”, and the data is right there!

Once you’ve set up a table as a system versioned temporal table, it just takes a simple query to see the history of changes for a row. While it complicates the process of managing the table structure as it changes, it’s a great way to track those changes over time.

The problem is, you have little control over the data in the history table (at least while history capture is enabled). It’s just an automatic process that captures every single change. Even the non-effective ones are captured, like when you update the same row multiple times at the same time, or in the same transaction.

That automatic process stamps the row with the time in the system when the row was changed. This is a problem because the system time is only when the change occurred in your database – not when the actual thing happened outside of the SQL database DML (data manipulation language) operation.

While system-defined temporal tables are wonderful for tracking DML modifications, they are not ideal for usage where you need any control over the past. For example, if you determine that an address was misspelled in past data, you can’t simply update it without disabling system versioning.

What happens if you attempt to update the history table in SQL Server?

If you do attempt to update the history table, you’ll get this error:

The same can be said of the time of the change. When tracking DML changes, you want to set the time at the exact moment you make the change. But, if you want changes to appear as if they occur at the end of the day, for example, this can’t be done and still use the AT SYSTEM TIME construct. Again, you’d have to turn off the system versioning to enable this.

So, this request would be to give us tables that are the same as the current temporal tables but, instead of everything being automated, let the user specify the end start and/or end ones in the insert/update. Then, let the user be able to update the history in a relatively simple manner. My preference would be to leave it to us to write the code ourselves!

Here’s a couple of ways it could work:

At this point, the table is basically just a Type 2 dimension that lets us use the amazing syntax and be able to query our price structures. Even better, why not let the user pre-load future data into the so-called “history table”?

Then, you could load up discounts (and get the current discount for a future order) using a construct similar to FOR SYSTEM TIME (FOR USER TIME?) – a construct that makes system versioned tables so very useful.

True domains in SQL Server

For as long as I can remember, we could create a type in SQL Server such as:

This creates an alias of VARCHAR(20) that is NOT NULL in a column declaration unless you specify NULL, regardless of your SET ANSI_NULL_DFLT_ON setting.

Then you can use it in a table create as:

The problem is, what about when I do this?

Can this value be NULL? Yes, it can. But what if I want to make sure any value is inserted into a column or variable declared with the dbo.PhoneNumber type conformed to a certain nullability and format?  

For a column, this currently has to be done in a CHECK constraint on any column that you want to enforce the format. Variables just have to repeat that format in code. This is tedious and error prone.

NOTE: There is a long-deprecated method of attaching a check constraint to a TYPE, but it still didn’t work on variables.

So, I would love to be able to do something like the following:

And then when I type…

…or…

…both will immediately fail. The first is for a violation of the CHECK expression, and the second is simply because NULL values aren’t allowed. This would make such structures very valuable for important validations.

A big SQL Server graph enhancement…or two

I understand that graphs in SQL Server are no longer high on the priority list for being updated. However, they’re supremely useful for modeling complex structures in a SQL database when you truly need it along with your relational data.

For example, you may be implementing a customer management system (CRM). You need the ability to connect a customer to other customers, accounts, sales, etc. This will give you crucial information about customer influence so you can see how the salesperson made that big sale.

You can model it as something like this:

Note: this simple method assumes all structures in the same schema. The object could be identified with the object_id instead, but that is even more annoying to work with!

From my experience with using a design like this, it’s very annoying to query beyond all imagination. Enter SQL Server’s graph structures. They allow you to keep these details in the relationship so you can fan out connections between objects using a (slightly unpleasant) syntax.

What I mean by ‘slightly unpleasant’ is that it’s not as obvious as most T-SQL. It does work quite well to allow you to see multiple types of data in one query, but this takes a bit of configuration.

That’s where the following feature suggestions come in. These additions could lead to graph types needing less and less code – code that doesn’t use the graph features in a natural manner. After all, writing recursive CTEs (common table expressions) – or even just looping code – is quite tedious.

Here are the three features I have in mind:

Filtered shortest path

SHORTEST_PATH was a great improvement in SQL Server 2019, making graph objects a rather decent tool for some applications. The problem is, you often want to find a path that isn’t simply just the shortest through all nodes.

So, my suggestion is a simple filter on the SHORTEST_PATH calculation to filter out (or in) certain nodes in the path. This would allow you to ask for the shortest path from Customer 1 to Customer 10 through, or not through, a certain sales order, customer, or any node that has edges between them.

Bonus points might be to allow some ordering to the nodes be required, like from Customer 1 through Sales Order X and Customer Y, but not in the reverse order. This is far less necessary, of course.

Weighted path

Probably the feature that would be most exciting is the ability to do a weighted path. The idea here is that nodes have a cost or value associated with them. Orders might have a cost, distance between nodes, etc. So, instead of calculating the shortest path based on the number of edges between two nodes, each edge has a value that you need to add up to see what the overall cost is.

For example, using a graph structure for booking flights. If you have all the routes you can get from one city to another, you can currently find the most direct flights from city to city using the SHORTEST_PATH syntax. But what about if there are two direct flights – or another with two options on top of that?

Elsewhere, using our CRM example from before, you might want to find the connection between Customer 1 and Customer 10 through the “best” customer in total. This even comes into play when comparing two paths of the same length.

Say Customer 1 and 10 both know Customer 3 and 4. 3 has never purchased anything, but 4 has. As SHORTEST_PATH works now, either node would satisfy the shortest path – not necessarily the most desirable.

All paths

Finally, just allowing us to see every path in the output would be huge. OK, most of the time we just want to see that a node connects to a node at all – but the more you can see all the paths, the better. Plus, you can also do some of the things mentioned in the other two items yourself.

This, of course, could be quite a costly operation. The feature would therefore need to be limited in the sense of just how far and wide it could work.

However, of the three, it could be the most useful – with the caveat that you couldn’t try to process a large set of data. Like trying to calculate the Six Degrees of Separation from someone you know who is in IMDB to Kevin Bacon.

Strict compilation mode in SQL Server

The addition of a strict compilation mode to SQL Server is a common request (also see Erland’s list.) T-SQL is not a compiled programming language – rather it is interpreted – so the code is very “loose”.

There are plenty of benefits to “loose” code – you can even create the code without the table yet existing. And, in some cases, you may be using a table created before your code executes and is then dropped.

However, I don’t think anyone wants this to be a requirement for writing T-SQL. Due to object linking – which happens when you execute your code – it would greatly complicate managing SQL Server objects.

So, for example, if you write this statement…

…and then execute it, it will be parsed, verified, and only at that point will it check to see if table_name actually exists. This isn’t really noticeable when executing an ad-hoc statement, because it’s instantaneous.

What about when you create a stored procedure?

However, when you create a stored procedure…this is when it gets kind of weird at times. You can create either of the following objects on any SQL Server database where you have right to create a procedure/scalar function…

…or…

Both of these will be created without error but, if you try to use either, you’ll receive an error that includes the following message (assuming you don’t have a table_name object in your dbo schema with a column named column_name!):

However, when you try to create a VIEW object with the same code…

…you immediately get this error:

My suggestion is for something like a setting, so you can be sure that all dependencies exist. Perhaps something like:

Now, the procedure or function creation would give the same failure because I’ve asked it to. Ironically, it does give you textual warnings when trying to use a non-existent procedure in a new stored procedure, but not one that causes failure.  

Again, handy when you’re trying to build your objects out of order. It’s less ideal, however, when you’re creating and deploying them, some object isn’t there yet, and your object is created and doesn’t complain at all.

This one actually just caught me out on the day I finished writing this. I was creating a group of tables and stored procedures to use them but missed a table and the process failed. In DEV, of course…

CREATE without starting a new batch in SQL Server

This is not one of the most important feature suggestions in this list. However, its absence does cause quite a bit of extra typing when you’re creating new objects.

Some things, for example, require their own batch – leading you to write some code that just feels…wrong. For example, if you want to create a new stored procedure, you can’t just write…

…or you’ll receive the error Incorrect syntax near the keyword ‘PROCEDURE’. I get why this happens, in some cases, because BEGIN and END are not required, so they really couldn’t know when it starts and stops.

I don’t want them to make a huge breaking change and start this as a requirement, though. I’d just love for it to be resolved in some manner.

It could be a new BEGIN OBJECT… END OBJECT syntax perhaps. There’s probably even something in the standards for this. Without this sort of syntax, however, extra code/comments often end up in the procedure you’re writing.

The most annoying case: the SQL Server CREATE SCHEMA statement

The CREATE SCHEMA statement is the one that is done the most, and it’s a far simpler syntax than any other version of this issue. This is because it’s quite often the one someone wants to create for a new schema to install some code. The fact that the statement needs to be in its own statement is a pain.

Yes, I do know you can create the schema and objects in one statement but, even then, you can’t CREATE OR ALTER SCHEMA. What I want to be able to do is just…

…all in the same batch.

Make it easier to use extended properties in SQL Server

I often use extended properties in my SQL Server databases, mainly to document stuff that I’ve put into data models. It’s not easy, though. Even just generating code to create properties can be really painful.

For example, to add a property to a column, you need to use the following syntax:

That is a lot of typing just to add a simple comment. I am not saying this loose hierarchy way of adding properties is bad. There’s just plenty of times where it would be nice to have a more typical one or two value addresses. Ones that are already pre-named for you.

For example, maybe something like the following for all the basic object types…

…and for their columns…

And – while we’re at it – maybe a COLUMN_ID function that works like OBJECT_ID, but takes a qualified column name instead of an object? How about the same for an index?

Easier dropping of dependencies

This is simple but would be really nice sometimes. I’m currently working on a project in Microsoft Fabric’s implementation of T-SQL where I’m generating lots of objects and code. Often, I just want to drop the schema and everything within it. I have scripts that can do this, of course, but if I could just type…

…that would be awesome, especially when working with test databases. I could also see there being levels, like CASCADE INTERNAL, that would still fail on references outside of the schema. I don’t see this happening anytime soon, though, as AI is making it easier and easier to write such code.

The aforementioned script to do the cascading is something I just asked SQL Prompt to do. It spit out a set of queries to do the work for me. But while that is simple enough in an environment that I don’t care much about, the script would need plenty of testing in a production or test setting before being used in a manual deployment.

Autonomous transactions in SQL Server

Autonomous transactions: another feature I’ve oft wanted to have a user-controlled version of in SQL Server.

As far as the user is concerned, when you execute BEGIN TRANSACTION, just one transaction has been started. You do have to execute COMMIT TRANSACTION once for every time you start a transaction but, if you want to undo that transaction, it just takes one ROLLBACK TRANSACTION.

However, during your transaction’s execution, there are a few things that aren’t subject to that transaction. For example, IDENTITY values that are part of a rolled-back transaction are burnt and will not be reused.

SEQUENCE values are the same. While they have some form of transaction going on for consistency (so nobody gets the same value), they can’t be rolled back; that would require other transactions to wait for their completion.

Sometimes it would be wonderful to have that kind of power for our code, especially for error handling. Better still, imagine if you could start at least one transaction that we separate from the main transaction. Even if you could only do this for one statement, it would be an amazing improvement.

Say, for example, you have this code…

…and now you want to write a CATCH block to log that error into your error table. Your transaction is in a doomed state when you reach the error handler. You can’t just write the error log table and expect it to be there after you roll back.

So ideally you could do something like the following (with more precision, transaction naming etc):

Logging errors (and the like) would be one of the main uses for this feature, but there are others. Capturing values of parameters and columns, for example. And knowing when and where AFTER triggers, and identity and sequence values, have been generated. All of these would make troubleshooting so much easier.

Conclusion

Ultimately, all of the SQL Server coding changes I’ve suggested in this article would be “nice to haves”. Some more so than others, but none are 100% essential – yet they would all make programmers’ lives easier. And, as one such (relational) programmer, this is what I hope for.

In summary, I hope to see:

Better internals…

…and the less we relational programmers need to know about the internals, the better! You write a query, send it to the engine, and it returns you an answer without you knowing how. That is the ultimate goal.

Better administrative tools…

…from integration to security, high availability, system integration, etc. Stuff to make our systems just plain better.

T-SQL improvements…

…keeping up with other data platforms and beyond…

Finally, I’ll admit that my interest lies in T-SQL. It’s what I have done, and what I expect to do, until the day I put my keyboard out to pasture and only use a remote control.

Some of my feature suggestions are bigger/more complex than others, but they would all have a dramatic effect on how easy it is to use SQL Server – and thus the ultimate value it provides to the everyday user.