What do you do in SQL Server to CREATE OR ALTER?

Question

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
           WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' 
             AND ROUTINE_SCHEMA = 'dbo' 
             AND ROUTINE_TYPE = 'PROCEDURE')
 EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')

CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS BEGIN
    -- body
END

For triggers, you have to lean on the proprietary system views.

Is this the most accepted convention in the meantime?

EDIT: As n8wrl suggested, the official word suggests that this feature is not a high priority. Hence the question.

Solution 1

This article makes a good point about losing permissions when dropping an object in SQL server.

• Tips ‘N’ Tricks – T-SQL – An Elegant way to CREATE or ALTER Stored Procedures in One-Go for Easy Maintenance

So here is the approach which retains permissions:

IF OBJECT_ID('spCallSomething') IS NULL
    EXEC('CREATE PROCEDURE spCallSomething AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE spCallSomething ... 
--instead of DROP/CREATE

Also works for functions, just replace PROCEDURE with FUNCTION in the above code.

Another reason to consider doing it this way is tolerance to failure. Suppose your DROP succeeds, but your CREATE fails - you end with a broken DB. Using ALTER approach, you will end up with an older version of the object.

Solution 2

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE.

The year is 2016 and it does now have DIE (Drop If Exists) in SQL Server 2016 RTM and CREATE OR ALTER (introduced in 2016 SP1).

Taking Drop If Exists first the caveats around needing to re-apply permissions with this approach still apply. Example syntax is

DROP PROCEDURE IF EXISTS dbo.SynchronizeRemoteCatalog

GO

CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS
  BEGIN
      BODY:
  END 

GO

/*TODO: Reapply permissions*/

CREATE OR ALTER retains the permissions. Example syntax is

 CREATE OR ALTER PROCEDURE dbo.SynchronizeRemoteCatalog
 AS
 BEGIN
   BODY:
 END

The corresponding MSSQL Tiger Team blog post explains

CREATE OR ALTER can be used in programmability objects such as:

• STORED PROCEDURES (including natively compiled)
• FUNCTIONS (Transact-SQL, including natively compiled)
• TRIGGERS
• VIEWS

But cannot be used in:

• Objects that require storage (tables, indexes and indexed views)
• CLR user-defined functions
• Deprecated programmability objects (RULE and DEFAULT)
• Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE - SCHEMA). On these objects, the syntax for CREATE and ALTER is very different from a syntax and usability perspective.

Solution 3

We encountered a situation where we needed to update a remote site, but we didn’t have DROP permissions. Until now, we have been using the ‘DROP and CREATE’ script built into SSMS 2008 R2, but now we needed to change. We created three templates, which we drop above the appropriate ALTER scripts when we need to update a stored procedure or function:

—- Stored Procedure
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE PROCEDURE [dbo].[<Name_Of_Routine, , >] AS SET NOCOUNT ON;')
EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;')
GO

—- Scalar Function
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS INT AS BEGIN RETURN 0 END;')
EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;')
GO

—- Table-based Function
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS @O TABLE(i INT) AS BEGIN INSERT INTO @O SELECT 0 RETURN END;')
GO

Any special permissions get scripted after each CREATE (Table functions cannot be assigned permissions). After that, the ALTER doesn’t change it, and if they add or modify the permissions, they remain. Doing it this way, it’s an easy task to copy the name of the function or stored procedure, and use the Template Parameter replacement to automating the completion of these scriptlets.

Now, I’m hoping that the good folks at Microsoft will either add this to their “Script ___ as” lists, or give us the ability to create our own such that this scripting comes ‘baked-in’

You may want to throw some weight behind the SQL Server feedback entry at: https://connect.microsoft.com/SQLServer/feedback/details/344991/create-or-alter-statement. It seems to be one of the few that are still accessible publicly, and they state that they "have started a feasibility review for this to decide if we can ship this in the near future." The more voices, the more likely this will happen!

(Update: now also using the following code for Triggers and Views)

-- Triggers
IF OBJECT_ID('[dbo].[<Name_Of_Trigger, , >]') IS NULL -- Check if Trigger Exists
    EXEC('CREATE TRIGGER [dbo].[<Name_Of_Trigger, , >] ON [<Name_Of_Table, , >] AFTER UPDATE AS SET NOCOUNT ON;') -- Create dummy/empty SP
GO

-- Views
IF OBJECT_ID('[dbo].[<Name_Of_View, , >]') IS NULL -- Check if View Exists
    EXEC('CREATE VIEW [dbo].[<Name_Of_View, , >] AS SELECT 1;') -- Create dummy/empty View
GO

Solution 4

Every time a developer writes IF EXISTS(...) DROP a seal pup is clubbed. You should know exactly what's in the database and your upgrade script should do the CREATE or ALTER as appropriate, based on the current version of your application schema: Version Control and your Database.

Solution 5

I'd use OBJECT_ID(...) IS NOT NULL before a DROP.

Object identifiers have to be unique, so it works without using system tables:

CREATE TRIGGER dbo.ExistingTable ON dbo.AnotherTable FOR UPDATE
AS 
SET NOCOUNT ON
GO

gives

Msg 2714, Level 16, State 2, Procedure MetaClass, Line 3
There is already an object named ExistingTable ' in the database.

I normally use ALTER because of how we work with source control, etc.

View more solutions

Comments

[ruffin]

Looks like, a short seven-plus years later, SQL Server now has CREATE OR ALTER. Woohoo.

[harpo]

@ruffin, yep, that's what I do now. The approaches here are still the best option for earlier versions (which have a long half-life).

[Icegras]

Yay for CREATE OR ALTER, but that's SQL Server 2016 only.

[marc_s]

I would recommend using a) the "sys" schema, and b) a more focused system view, e.g. sys.tables for tables, sys.triggers for triggers etc., instead of just the generic "sysobjects" (which will be deprecated soonish).

[harpo]

I started using EXEC because sometimes when I sent the script to clients where the objects did not exist, SQL Server would complain. Maybe it's superstition, but it hasn't hurt anything.

[marc_s]

why? Better be safe than sorry! Have you never had a script fail on you and suddenly a sproc you were about to drop is still lingering around in the database? I'd always do a IF EXISTS() check first - just to be on the safe side! Have you never had a silly temp-DBA run some of your scripts several times instead of just once??

[n8wrl]

I find this to be a reasonable approach to allowing my deployment scripts to be run many times safely. Besides, if I can club a seal without getting cold...

[harpo]

I guess I don't live in the ideal world. What do I say if a script raises an error on a client's machine, even after versioning and testing? "Too bad. It should work, so I can't help." I'd be out of business, Remus.

[Remus Rusanu]

@marc: yes, silly dbas... I'm just trying to pland a seed here, get ppl to think at the db more as a resource under version control rather than the 'lets open ssms and modify out table!'. I know in practice is impossible not to rely on if exists() (or objec_id is not null, which I actually prefer, like gbn). I guess seal pups are doomed... arctic vile pest

[ZygD]

I did not know OBJECT_ID is proprietary... it's probably in Sybase too though

[harpo]

Yep, that's exactly how I've been doing it for a while now (except the AS SET NOCOUNT ON, that's an interesting twist). Come to think of it, I should set up a yasnippet for this pattern because I type it so much.

[Victor Zakharov]

How would you write the query? If object id is not null then what? Drop object? Is there such a statement in T-SQL?

[ruffin]

(Realizing this is zombiing a thread) I'm not sure you can answer the question, "What do you do in SQL Server to CREATE OR ALTER?" with, "Don't CREATE OR ALTER in SQL Server". ;^) Well, I mean, I guess you did, but I'm not sure you should... "The first rule of CREATE OR ALTER Club is, 'Don\'t CREATE OR ALTER CREATE OR ALTER Club!!'"

[Daniel Bragg]

I have written templates to deal with the creating of missing stored procedures, scalar functions and table functions, with good success.

[Daniel Bragg]

However, the syntax is incorrect if you "just replace PROCEDURE with FUNCTION". I have posted our examples below.

[harpo]

Thanks, good point. In cases where uptime is sensitive, I've taken to doing the DROP/CREATE inside of a transaction, which I think is effectively the same as ALTER. But yes, ALTER is more surgical.

[deniz]

Why is "SET NOCOUNT ON" a good choice for the temporary placeholder sproc body? Asked here: stackoverflow.com/questions/40849374/…

[LeBaptiste]

I think there is also another excellent reason for using this approach is to avoid mistake concerning potential privileges already setup, e.g. a person other than the DBA has made a change, using drop/create you might end up removing granted privilege you were not aware of.

[vlabatut]

For the lucky guys that have SQL 2016 SP1 and above, this is now the way to go :)