{"id":104869,"date":"2025-01-01T21:41:17","date_gmt":"2025-01-01T21:41:17","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104869"},"modified":"2026-04-30T09:19:38","modified_gmt":"2026-04-30T09:19:38","slug":"five-changes-to-sql-server-id-love-to-see","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/five-changes-to-sql-server-id-love-to-see\/","title":{"rendered":"Five SQL Server changes I’d love to see (DROP_EXISTING, ALTER TABLE, STRING_SPLIT fixes)"},"content":{"rendered":"\n

In this article,<\/strong> Aaron Bertrand lists five SQL Server behaviors that trip developers up, and that he would like Microsoft to fix<\/strong>:<\/p>\n\n\n\n

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

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

(3) STRING_SPLIT requires compatibility level 130, locking out databases still on 100; <\/strong>

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

(5) a ‘bypass_transaction_log’ or ephemeral-table declaration would eliminate log growth for staging and transient workloads.<\/strong> <\/p>\n\n\n\n

Each section names the specific pain point with a working code example of the current behavior.<\/strong> Read on for more.<\/strong><\/p>\n\n\n\n

Five SQL Server changes I’d love to see<\/h2>\n\n\n\n

I\u2019ve 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\u2019s fun every once in a while, to be whimsical about the way I wished<\/em> SQL Server worked. Especially in January because, you know, new beginnings.<\/p>\n\n\n\n

So here are a few ideas that have crossed my mind recently:<\/p>\n\n\n\n

<\/a>DROP_EXISTING<\/h2>\n\n\n\n

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\u2019m targeting. If I try to create an index from a script, say, that\u2019s in source control after a deployment to a different environment:<\/p>\n\n\n\n

CREATE INDEX x ON dbo.foo(bar) WITH (DROP_EXISTING = ON);<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Msg 7999, Level 16, State 9, Line 6<\/code>
Could not find any index named 'x' for table 'dbo.foo'.<\/code><\/p>\n\n\n\n
If I change the script to (DROP_EXISTING = OFF<\/code>), I can create the index. But then if I want to tweak it, say, add an INCLUDE<\/code> column or a filter, unless I remember to change it back to ON, now the result is:<\/p>\n\n\n\n
Msg 1913, Level 16, State 1, Line 6<\/code>
The operation failed because an index or statistics with name 'x' already exists on table 'dbo.foo'.<\/code><\/p>\n\n\n\n
I want more convenience here, like CREATE OR ALTER PROCEDURE<\/code> affords us. Instead, I have to make the script idempotent manually, e.g.<\/p>\n\n\n\n
IF EXISTS (SELECT 1 FROM sys.indexes WHERE \u2026)\n  CREATE INDEX \u2026 WITH (DROP_EXISTING = ON);\nELSE\n  CREATE INDEX \u2026 ;<\/pre>\n\n\n\n
That\u2019s 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:<\/p>\n\n\n\n
CREATE INDEX \u2026 WITH (DROP_EXISTING = IF_EXISTS);<\/pre>\n\n\n\n
Or even:<\/p>\n\n\n\n
CREATE INDEX \u2026 WITH (DROP_IF_EXISTS = ON);<\/pre>\n\n\n\n
This would mean all of my index scripts could be structured the same!<\/p>\n\n\n\n
<\/a>ALTER TABLE<\/h2>\n\n\n\n
In a recent discussion, I was reminded that this inconsistency between adding and dropping a column still exists:<\/p>\n\n\n\n
ALTER TABLE ADD <column name> \u2026;\nALTER TABLE DROP COLUMN <column name> \u2026;<\/pre>\n\n\n\n
Would it kill them to make the column keyword optional?<\/p>\n\n\n\n
ALTER TABLE ADD [COLUMN] <column name> \u2026;\nALTER TABLE DROP [COLUMN] <column name> \u2026;<\/pre>\n\n\n\n
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\u2019t always get on board with the notion of \u201cit is this way simply because it\u2019s always been that way.\u201d I don\u2019t like that reason either.<\/p>\n\n\n\n
<\/a>STRING_SPLIT<\/h2>\n\n\n\n
I really don\u2019t like that functions like STRING_SPLIT()<\/code> require you use the130 compatibility level, since some holdouts could still be using 100 on SQL Server 2022 databases. Since we\u2019re 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()<\/code>.<\/p>\n\n\n\n
So, you could have master in the right compatibility level and just call master.sys.string_split()<\/code> 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\u2019d all implement our versions slightly differently. And it\u2019s amazing how many people just refuse to create their own objects for anything (see numbers and calendar tables).<\/p>\n\n\n\n
Along those lines, it would be great to have wrapper functions around GENERATE_SERIES()<\/code>, too. Maybe that could even translate to and from JSON or CSV.<\/p>\n\n\n\n
And a generic TVP built-in so that you don\u2019t have to create your own TVPs in every database. For example, I\u2019d love to just have a stored procedure parameter that automatically presents as a table of int<\/code> or bigint<\/code> values, etc.<\/p>\n\n\n\n
CREATE PROCEDURE dbo.myprocedure\n  @IDList sys.bigints READONLY\nAS\n  SELECT Id, Title \n   FROM Posts \n   WHERE Id IN (SELECT value FROM sys.bigints);<\/pre>\n\n\n\n
(Likewise, it would be cool if we could ditch the required READONLY<\/code> keyword here, too; in general, but especially for system types.)<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
<\/a>Better statement scoping<\/h2>\n\n\n\n
In a recent post, I talked about how Oracle now allows you to reference GROUP BY aliases at the same scope<\/a>, which would be a powerful thing to add to SQL Server (but don\u2019t hold your breath). Itzik Ben-Gan talked about the new WINDOW clause in SQL Server 2022<\/a>, which allows you to reference the same window specifications more than once without having to repeat all of the logic.<\/p>\n\n\n\n
I\u2019d love the same thing for the output<\/em> of window functions, like, say:<\/p>\n\n\n\n
SELECT SomethingID, \n     rownum = ROW_NUMBER() OVER (...),\n     maxdate = MAX(date) OVER (...)\n  FROM dbo.Something
  WHERE rownum <= 10 AND maxdate < '20240101';<\/pre>\n\n\n\n
It\u2019s cumbersome to have to create a CTE or subquery just to avoid repeating expressions\u2026 especially when the queries are much more complex than this simple example.<\/p>\n\n\n\n
<\/a>Selective skip rollback<\/h2>\n\n\n\n
And finally, I wish you could declare a table as ephemeral or a database or DML statement as \u201cbypass_transaction_log\u201d so that the logging you do (for example, within a trigger) could survive a rollback.<\/p>\n\n\n\n
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\u2019re logging change types). It also encourages people to use hacky ways to log data so that it can\u2019t be rolled back or develop more cumbersome ways to implement their own equivalent to transactions.<\/p>\n\n\n\n
<\/a>And you?<\/h2>\n\n\n\n
What are some of your wish list items for how SQL Server should work?<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Simple Talk is brought to you by Redgate Software<\/h3>\n                
\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                                            Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs<\/h2>\n\n                        
1. What does the DROP_EXISTING = ON error mean in SQL Server?<\/h3>\n            
\n                
DROP_EXISTING = ON requires the index to already exist on the table. If the index is not present (for example on a fresh deployment to a new environment), SQL Server returns ‘Msg 7999 – Could not find any index named X for table Y’. The opposite case is also a problem: if the index exists and DROP_EXISTING is OFF, SQL Server returns ‘Msg 1913 – The operation failed because an index or statistics with name X already exists’. There is currently no CREATE OR ALTER equivalent for indexes – developers have to either drop-then-create, wrap in IF EXISTS, or manage the script with external tooling.<\/p>\n            <\/div>\n                    
2. Why is ALTER TABLE DROP COLUMN inconsistent in SQL Server?<\/h3>\n            
\n                
ALTER TABLE supports DROP COLUMN but does not support a clean ‘ADD COLUMN IF NOT EXISTS’ variant. To make a column addition idempotent, you have to wrap the ALTER in an IF NOT EXISTS check against sys.columns or use dynamic SQL. DROP COLUMN on a non-existent column similarly requires a wrapper check. This asymmetry makes deployment scripts more verbose than they need to be and is a common source of developer frustration.<\/p>\n            <\/div>\n                    
3. Why does STRING_SPLIT require compatibility level 130 in SQL Server?<\/h3>\n            
\n                
STRING_SPLIT was introduced in SQL Server 2016 at compatibility level 130. Databases still running on compatibility level 100 or 110 – which is common for migrated legacy databases – cannot use STRING_SPLIT without a database-level compatibility change. Raising compatibility level can have other query plan side effects that DBAs want to control independently, so the two concerns become tangled. Many developers would prefer STRING_SPLIT to be available at any compatibility level on SQL Server 2016+ instance.<\/p>\n            <\/div>\n                    
4. Can you use GROUP BY alias at the same scope in SQL Server?<\/h3>\n            
\n                
No. In SQL Server, an alias defined in the SELECT list cannot be referenced in the same query’s GROUP BY, HAVING, or WHERE clauses – the alias is only visible in ORDER BY. You have to either repeat the expression or wrap the query in a subquery or CTE. Oracle allows SELECT-list aliases in GROUP BY at the same scope, which is the behaviour many SQL Server developers would like to see adopted.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Aaron Bertrand on five SQL Server behaviours he’d like Microsoft to fix: DROP_EXISTING needing an existing index, ALTER TABLE DROP COLUMN inconsistency, STRING_SPLIT compatibility level requirements, GROUP BY alias scoping, and bypass transaction log for ephemeral tables.…<\/p>\n","protected":false},"author":341115,"featured_media":104870,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531],"tags":[4151],"coauthors":[158980],"class_list":["post-104869","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-t-sql-programming-sql-server","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104869","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341115"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104869"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104869\/revisions"}],"predecessor-version":[{"id":110215,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104869\/revisions\/110215"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104870"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104869"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}