In this article, Aaron Bertrand lists five SQL Server behaviors that trip developers up, and that he would like Microsoft to fix:

(1) DROP_EXISTING requires the index to already exist – create-or-replace semantics would be simpler;

(2) ALTER TABLE allows DROP COLUMN but the reverse ‘ADD COLUMN IF NOT EXISTS’ is inconsistent;

(3) STRING_SPLIT requires compatibility level 130, locking out databases still on 100;

(4) GROUP BY aliases from the SELECT list can’t be referenced at the same query scope (Oracle allows this);

(5) a ‘bypass_transaction_log’ or ephemeral-table declaration would eliminate log growth for staging and transient workloads.

Each section names the specific pain point with a working code example of the current behavior. Read on for more.

Five SQL Server changes I’d love to see

I’ve had lots of wish lists over the years, from Ladybug to Connect to UserVoice, and very few of my dreams ever come true. Still, it’s fun every once in a while, to be whimsical about the way I wished SQL Server worked. Especially in January because, you know, new beginnings.

So here are a few ideas that have crossed my mind recently:

DROP_EXISTING

I am constantly fiddling with indexes on our biggest database. It sometimes takes me 4 or 5 iterations on an index definition before I hit the sweet spot for the queries I’m targeting. If I try to create an index from a script, say, that’s in source control after a deployment to a different environment:

Result:

Msg 7999, Level 16, State 9, Line 6
Could not find any index named 'x' for table 'dbo.foo'.

If I change the script to (DROP_EXISTING = OFF), I can create the index. But then if I want to tweak it, say, add an INCLUDE column or a filter, unless I remember to change it back to ON, now the result is:

Msg 1913, Level 16, State 1, Line 6
The operation failed because an index or statistics with name 'x' already exists on table 'dbo.foo'.

I want more convenience here, like CREATE OR ALTER PROCEDURE affords us. Instead, I have to make the script idempotent manually, e.g.

That’s bulky and hard to maintain and requires you to repeat all the other properties of the index (or use dynamic SQL).. It sure would be nice to have idempotent syntax for the first and subsequent times I create a given index, like:

Or even:

This would mean all of my index scripts could be structured the same!

ALTER TABLE

In a recent discussion, I was reminded that this inconsistency between adding and dropping a column still exists:

Would it kill them to make the column keyword optional?

This way, whether you like the explicit or implicit variation, your scripts can be congruent. I think this would reduce confusion and friction for new users who can’t always get on board with the notion of “it is this way simply because it’s always been that way.” I don’t like that reason either.

STRING_SPLIT

I really don’t like that functions like STRING_SPLIT() require you use the130 compatibility level, since some holdouts could still be using 100 on SQL Server 2022 databases. Since we’re now in an era where we will keep old compat levels around forever, it would be nice to have a system function that just wrapped STRING_SPLIT().

So, you could have master in the right compatibility level and just call master.sys.string_split() and the built-in function would run in that context. We could create our own, of course, but only some of us would, and we’d all implement our versions slightly differently. And it’s amazing how many people just refuse to create their own objects for anything (see numbers and calendar tables).

Along those lines, it would be great to have wrapper functions around GENERATE_SERIES(), too. Maybe that could even translate to and from JSON or CSV.

And a generic TVP built-in so that you don’t have to create your own TVPs in every database. For example, I’d love to just have a stored procedure parameter that automatically presents as a table of int or bigint values, etc.

(Likewise, it would be cool if we could ditch the required READONLY keyword here, too; in general, but especially for system types.)

Better statement scoping

In a recent post, I talked about how Oracle now allows you to reference GROUP BY aliases at the same scope, which would be a powerful thing to add to SQL Server (but don’t hold your breath). Itzik Ben-Gan talked about the new WINDOW clause in SQL Server 2022, which allows you to reference the same window specifications more than once without having to repeat all of the logic.

I’d love the same thing for the output of window functions, like, say:

It’s cumbersome to have to create a CTE or subquery just to avoid repeating expressions… especially when the queries are much more complex than this simple example.

Selective skip rollback

And finally, I wish you could declare a table as ephemeral or a database or DML statement as “bypass_transaction_log” so that the logging you do (for example, within a trigger) could survive a rollback.

Currently, the workaround is to write your logging to a table variable, then roll back, then transfer the data from the table variable to a permanent log table. This is messy and causes maintenance issues (for example when the columns you’re logging change types). It also encourages people to use hacky ways to log data so that it can’t be rolled back or develop more cumbersome ways to implement their own equivalent to transactions.

And you?

What are some of your wish list items for how SQL Server should work?