I Wish SQL Server Had These Four Innovations.
I love SQL Server, and I’m excited for the release of 2025. I think the query processing keeps getting better, which means your apps are gonna go faster, with less code changes.
The AI stuff is a little fluffy, and I don’t think time will be kind to it, but I totally understand why Microsoft felt like they had to chase that buzzword. All the cool kids are doing it, just like they did blockchain and IOT, and Microsoft is always looking for any way they can to keep SQL Server relevant to developers.
However, I keep seeing stuff in other databases that really is innovative, and would genuinely make developers’ lives easier, and I find myself wishing that Microsoft would focus on these kinds of real-world usability improvements. Here are just a few that have come across my desk recently.
Optional From-First Syntax
Imagine being able to write your queries like this:
|
1 2 3 4 5 |
FROM dbo.Users u INNER JOIN dbo.Comments c ON u.Id = c.UserId SELECT u.Location, u.Reputation, c.Score, c.Text WHERE u.DisplayName = N'Brent Ozar' ORDER BY c.Score DESC; |
If you wrote the from and joins first, then IntelliSense would actually be useful, because the select/where/order would all understand where you’re pulling data from. I am so sick and tired of starting my queries by typing:
|
1 2 |
FROM dbo.Users u INNER JOIN dbo.Comments c ON u.Id = c.UserId |
And then up-arrowing back up to the top to type SELECT, because now I get autocomplete. I have to do this so freakin’ often that the up and down arrows on my keyboard are visibly more worn out than most:
The only other keys that are even more worn out are the ones on the left side, and I’ll leave the reason why as an exercise for the reader:
DuckDB lets you type FROM first, but best of all, it’s optional – not required – so your queries work either the old school way, or the new from-first way. It’s a great way to gradually implement a better querying experience.
Native Rest or GraphQL API
Microsoft’s Azure Data API Builder sits in between your apps and your database. The DBA sets up the DAB, defining which tables people are allowed to query and update, and how joins are formed. It’s like a reporting universe, but for your applications.
Then, developers simply hit the API with REST or GraphQL requests like:
|
1 |
GET http://sqlserver:5000/api/Actor |
And get easy-to-consume JSON result sets. It supports filtering, sorting, joins, etc, and even better, it has pagination by default, encouraging good, scalable development practices.
Because both the API inputs and outputs are standards, this would be usable across not just SQL Server, but other Microsoft (and other!) database products, especially if Microsoft implemented it in stuff like Azure SQL DB and CosmosDB. Instead of having to build Tabular Data Stream (TDS) support into every app development platform, any dev platform that supported the REST or GraphQL APIs and JSON output would instantly have access to Microsoft’s best relational databases. Easier app development = better chance that Microsoft databases will get picked to play.
Yes, Microsoft built this innovation, and yes, they made it open source. But because it’s buried in with the rest of their open source projects, it’ll never get the marketing attention it needs, and big companies often refuse to install any add-ons. If it was part of the boxed product itself, I think it’d get rapid adoption for new development projects. Will it find its way into the boxed product? Probably not, because it won’t increase licensing sales.
Better Query Plans with Plan Stitching
Back in 2018, Microsoft published a research paper about plan stitching: breaking query plans up into parts, compiling the parts separately, and then stitching together the various parts at runtime. It’s kinda like OPTION (RECOMPILE), but inside the query itself, while it’s running.
This would be so helpful for those 20-table-joins I see so often in reporting queries, not to mention people who like to join half a dozen nested views together. SQL Server just doesn’t stand a chance of building an accurate overall plan in situations like that. Instead, I’d love for it to start pulling data back from the first couple of tables, and then make different decisions based on how many rows come back from each table.
The research looked promising, albeit challenging (and expensive) to actually implement and test. I understand why it didn’t make the cut, but that doesn’t stop me from wishing for it. However, the next innovation is less nice-to-have, and more come-on-why-the-hell-don’t-we-have-this-yet….
Automatic Backups, Encryption, and Patching
When you provision an AWS RDS SQL Server instance (and you get similar options in Azure SQL DB, I just happened to be provisioning an RDS instance so I was reminded of this), you get a simple set of checkboxes and dropdowns:
Company-wide settings policies like Cost Threshold, MAXDOP, trace flags, etc – just a matter of picking them from a dropdown. Automatic backups? Already checked for you, with continuous point-in-time backups managed by the robots. Encryption? Of course. Automatic patching? Sure, if you want (not on by default) and you can even pick the maintenance windows when you want them to happen.
It’s 2025. Why are we screwing around with vector data types when SQL Server still installs itself in such a vulnerable state, with no backups, no corruption checking, and no ability to patch itself, and not offer any options during setup to mitigate these glaring problems? I hate whatever product manager is letting that happen. There’s a word for them, what is it, trying to think of it…
13 Comments. Leave new
“A” and “S”. ‘sa’ default system admin account? Ouch.
Probably ‘AS’ in aliasing fields, tables, views etc.
“The only other keys that are even more worn out are the ones on the left side, and I’ll leave the reason why as an exercise for the reader:” — You prefer to turn to the left and runaway in FPS?
Absolutely on From First syntax. I always start with SELECT *, but the worn arrow key is the same. And absolutely x 2 on default installation state. 1 megabyte autogrowth by default is cringe-making for me when I find it in the wild.
*And then up-arrowing back up to the top to type SELECT, because now I get autocomplete*
I’m with Curtin Schafer (comment above), although to assuage the shame of “SELECT *”, I usually type “SELECT 1” . . means you get your assistance of choice for the JOIN columns, and besides – everyone knows FROM is way higher up the composition sequence that the particular columns, right?
The “From first” syntaxt is not necessarily a SQL Server (data engine) issue, it’s more a client (SSMS) issue. Whatever client application you’re using could allow that form of query construction, and convert it into valid T-SQL when it sends it to SQL Server. I tried looking at the official ANSI SQL-92 documentation to see if “From first” would be considered “legal” SQL format, but my eyes got blurry.
Think of it the same as the LINQ format (from…where…select) you can use with Entity Framework in a C#.Net program, and how the LINQ & EF libraries convert the LINQ code into T-SQL code when it needs to query SQL Server. If you could use LINQ as an alternate to T-SQL in a query window in SSMS, you could get Intellisense to do the fetching you’re asking for.
The DAB is indeed a great innovation, but one that PostgreSQL has had access to through PostgREST for almost 10 years. I think it’s awesome that it’s a first-class tool, but MS wasn’t the first to make it.
?This is why I read your blog, Brent. Please don’t go to retirement while I still have a job.
Huh, I’d have guessed the ‘What is the problem you are trying to solve’ keys would’ve been worn out the most.
For the optional FROM-first syntax, I slightly favor going further and placing the SELECT clause at the end.
FROM dbo.Users u
INNER JOIN dbo.Comments c ON u.Id = c.UserId
WHERE u.DisplayName = N'Brent Ozar'
ORDER BY c.Score DESC
SELECT u.Location, u.Reputation, c.Score, c.Text;
WASD? Brent is a gamer!
Ahh, in some ways I wish LINQ replaced SQL. It’s a more logical syntax for queries with FROM this FILTER that ORDER here SELECT these
SQL is such a weird language.
I spotted Copilot in Notepad recently and am just waiting for the day when MS replaces the Query Optimizer with AI. It’ll be great when execution plans become non-deterministic, yeah? (searches for poop emoji)
Hi Brent. Even though “Enable auto minor version upgrade” is an option available in the AWS RDS management console, I do not believe it actually does anything for SQL Server instances.
I find this to be confusing because AWS documentation currently claims it does:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.SQLServer.html
However, if we check the RDS CLI documentation, specifically for describe-db-engine-versions, we can see that the “AutoUpgrade” attribute is described as “Indicates whether the target version is applied to any source DB instances that have AutoMinorVersionUpgrade set to true.”.
https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-engine-versions.html
If we then check the AutoUpgrade value for all versions of SQL Server (standard edition, in this example) via the RDS CLI, all the versions that I can see return false, which would mean none of them are automatically applied. Here is a command for anyone with the CLI ready to rock, and let’s pretend we’re on 15.0.4430.1 for this example:
aws rds describe-db-engine-versions –engine sqlserver-se –engine-version 15.00.4430.1.v1 –query “DBEngineVersions[0].ValidUpgradeTarget[*].{AutoUpgrade:AutoUpgrade,EngineVersion:EngineVersion}” –output table
At the time of writing all versions shown in the output from the above CLI command reflect an AutoUpgrade value of “false”.
For comparison, at the bottom of this piece of AWS documentation, they provide example output of what a version looks like when it is enabled as an automatic upgrade target version. This example is for MySQL, but it’s a similar CLI call. Notice that one version is marked as “true” for the AutoUpgrade target version.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Upgrading.html
Also, it’s a bit old, but this SQL Shack article mentions the AWS documentation, at one point in time, stated that auto minor version upgrade is not supported by SQL Server. I am pretty darn sure I recall seeing it clearly stated in the documentation as well once upon a time, but I can’t find anything today, dang it.
https://www.sqlshack.com/performing-minor-and-major-version-upgrades-for-aws-rds-sql-server/
Lastly, for what it’s worth, I have a non-critical reporting RDS for SQL Server instance with auto minor version upgrade enabled for years and I do not recall it ever patching itself. You can trust me, I’m some internet rando.