Create or Replace

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…”:

Msg 2714, Level 16, State 3, Procedure XXX, Line 1 There is already an object named XXX in the database.

Msg 2714, Level 16, State 3, Procedure XXX, Line 1 There is already an object named XXX in the database.

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:

Msg 208, Level 16, State 6, Procedure XXX, Line 1 Invalid object name DBO.XXX

Msg 208, Level 16, State 6, Procedure XXX, Line 1 Invalid object name DBO.XXX

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.

Leave a Reply

Required fields are marked *.