Blog series: SQL Server Connect Items
How many times have you been forced to change a “CREATE” statement to “ALTER” because “There is already an object…”:
And then you need to change it back to “CREATE” in the script, or else it won’t work when deploying. This will happen if you try to issue a “ALTER” statement on a object that does not exist:
Oracle had it for, I don’t know, forever? And it’s one of the major annoyances when developing in SQL Server. But I’d prefer if the statement would be “CREATE OR ALTER” instead of “CREATE OR REPLACE”, because the latter implies that a new object will be created, loosing all security settings. “…ALTER” would do just what it sounds like, keeping permissions. Perhaps there is a need for both, as someone suggests in the feedback.
A common solution is to check if the object exists with IF EXISTS … DROP XXX… But that only helps with the “CREATE OR REPLACE” scenario, not “CREATE OR ALTER”.
This is the first in a serie of posts where I select an SQL Server connect item that get’s my vote. This certainly deserves it, altough voting has been disabled on this item. Maybe Microsoft thinks there are enough votes as it is?
You can find the connect item here. The connect item id is 127219.
