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

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.