{"id":111987,"date":"2026-08-24T12:00:00","date_gmt":"2026-08-24T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=111987"},"modified":"2026-08-17T12:37:24","modified_gmt":"2026-08-17T12:37:24","slug":"9-things-i-wish-sql-server-had-and-why-theyd-matter","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/9-things-i-wish-sql-server-had-and-why-theyd-matter\/","title":{"rendered":"9 things I wish SQL Server had (and why they’d matter)"},"content":{"rendered":"\n

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. <\/strong><\/p>\n\n\n\n

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

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

There’s also the recent wish lists from Ed Pollack<\/a> (I disagree with arrays, but strongly<\/em> agree with an OVERLAPS<\/code> operator!) and Greg Low<\/a> here on Simple Talk. <\/p>\n\n\n\n

Finally, I stopped looking around when I found Kevin Chant\u2019s<\/a> T-SQL Tuesday post on the topic. It occurred to me that, aside from Erland’s post (and Aaron Bertrand\u2019s <\/a>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.<\/p>\n\n\n\n

So, here are 9 things I wish SQL Server had<\/strong> – and a couple of them are kind of, sort of<\/em>, 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…!<\/p>\n\n\n\n

System managed columns in SQL Server<\/h2>\n\n\n\n

One of the things I want in most every table I create are (what I call) \u201cphysical-only\u201d 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.<\/p>\n\n\n\n

For example, I would like to be able to implement a simple row_created_time<\/code> and row_modified_time<\/code> using syntax more or less like the following:<\/p>\n\n\n\n

CREATE TABLE TableX\n(\n    \u2026 -- user columns\n     row_created_time  DATETIME2(0) NOT NULL \n            GENERATED ON INSERT AS (SYSDATETIME()),\n    row_modified_time DATETIME2 (0) NOT NULL\n            GENERATED ON INSERT, UPDATE AS (SYSDATETIME())  \n)<\/pre><\/div>\n\n\n\n
Now, whenever a row is added to TableX<\/code>, both row_created_time<\/code> and<\/em> row_modified_time<\/code> will be set to the current local time for the server. Whenever the row is updated, just the row_modified_time<\/code> would be set.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Of course, all of this can be done with an INSTEAD OF<\/code> trigger<\/a>, but it’s a lot of work to keep INSTEAD OF<\/code> triggers maintained as the structure of a table is changed over time. <\/p>\n\n\n\n
Using an AFTER<\/code> 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.<\/p>\n\n\n\n
User-defined temporal tables in SQL Server<\/h2>\n\n\n\n
System versioned temporal tables<\/a> have been a part of SQL Server since 2016 and are awesome to help you see what<\/em> has changed in your data over time. My coworkers and I love them. You can just ask \u201cwhen the heck did this change, and what was the previous value?\u201d<\/em>, and the data is right there!<\/p>\n\n\n\n
Once you’ve set up a table as a system versioned temporal table, it just takes a simple query<\/a> 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<\/em> those changes over time.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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<\/strong> – not when the actual thing happened outside of the SQL database DML (data manipulation language)<\/a> operation.<\/p>\n\n\n\n
While system-defined temporal tables are wonderful for tracking DML modifications, they are not<\/em> 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\u2019t simply update it without disabling system versioning. <\/p>\n\n\n\n
What happens if you attempt to update the history table in SQL Server?<\/h3>\n\n\n\n
If you do<\/em> attempt to update the history table, you’ll get this error:<\/p>\n\n\n\n
Msg 13561, Level 16, State 1, Line 54 Cannot update rows in a temporal\nhistory table 'tempdb.dbo.tablename'.<\/pre><\/div>\n\n\n\n
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\u2019t be done and<\/em> still use the AT SYSTEM TIME<\/code> construct. Again, you’d have to turn off the system versioning to enable this.<\/p>\n\n\n\n
So, this request would be to give us tables that are the same<\/em> 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<\/em> to write the code ourselves!<\/p>\n\n\n\n
Here’s a couple of ways it could work:<\/p>\n\n\n
\n