When scripting out SQL Server objects in earlier versions of SQL Server, you’ve historically had the option to do so with “CREATE”, “DROP and CREATE”, or as “ALTER” scripts. Each option has advantages over the others for maintaining your objects in multiple environments. But there are more versatile ways to preserve your object scripts that provide all the advantages of those options without the downsides that those historic options provided.
First it’s important to understand the pros and cons of CREATE vs. DROP/CREATE vs. CREATE vs. ALTER:
CREATE
- It works for creating your initial definition of your objects.
- If the object already exists in a database environment, CREATE will not overwrite it and will trigger an object already exists error.
- Not a viable option for saving your T-SQL object scripts as it can only be executed when the object does not already exist.
Example:
|
1 |
CREATE PROCEDURE dbo.spr_MyStoredProcedure AS SELECT * FROM MyTable |
DROP and then CREATE
- Requires the object to exist before the script can execute successfully.
- Removes all permissions and extended properties of the object when the object is dropped, thereby requiring you to reestablish any such permissions or extended properties after the create portion of the script executes.
- If the new CREATE portion of the script fails to compile you’re left in a state where the old object has been dropped but the new object has not been created.
Example:
|
1 2 |
DROP PROCEDURE dbo.spr_MyStoredProcedure CREATE PROCEDURE dbo.spr_MyStoredProcedure AS SELECT * FROM MyTable |
Conditionally DROP and then CREATE
- This idea checks for the existence of the object before it’s dropped then always recreates it, therefore the object does not have to exist prior to running a Conditional DROP and then CREATE.
- It’s great for completely redefining your object definitions.
- It’s flexible and allows you to maintain one script to initially create and maintain your objects.
- DROP and CREATE resets all permissions to the objects. Effectively you lose everything and must redefine all permissions, etc.
- If the new CREATE portion of the script fails to compile you’re left in a state where the old object has been dropped but the new object has not been created.
|
1 2 3 4 |
IF OBJECT_ID(‘dbo.spr_MyStoredProcedure’, ‘P’) IS NOT NULL DROP PROCEDURE dbo.spr_MyStoredProcedure CREATE PROCEDURE dbo.spr_MyStoredProcedure AS SELECT * FROM MyTable |
ALTER
- If the script for the new definition contains a compile error, the old object definition will remain unchanged. ALTER only overwrites the old definition if the new definition compiles successfully. This preserves a working version of the procedure in production environments.
- Can only be used to change existing objects definitions. ALTER cannot be used to create new objects.
- Preserves permissions from the existing object definition to the updated object definition.
- Not a viable option for saving your T-SQL object scripts as it can only be executed successfully when the object already exists.
Back in 1999 I created a hack that allowed me to get the best of both worlds by conditionally creating an object if it didn’t exist, and then altering the object afterwards. I found that if I created a shell stored procedure (or View or Function) I could alter that procedure with my definition – thereby having one “ALTER” script that maintained the definition of the object in a single script. Since CREATE must be the first statement in a batch, I worked around this by using the EXECUTE() statement within an IF block to conditionally create the object while keeping the CREATE as the first statement in the “batch” within the EXECUTE statement. In other words, I created a hack around not being able to conditionally create an object.
Conditionally CREATE then ALTER
|
1 2 3 4 5 6 |
IF OBJECT_ID(‘dbo.spr_SprocName’, ‘P’) IS NULL BEGIN EXECUTE (‘CREATE PROCEDURE dbo.spr_SprocName AS SELECT 1’); END; GO ALTER PROCEDURE dbo.spr_SprocName AS SELECT * FROM dbo.Tablename; |
- This hack has worked on all versions of SQL Server and continues to work through SQL Server 2016, but will not work in future versions of SQL Server once EXECUTE() is no longer supported.
- Does not require the object to exist before it will execute and it has all the advantages of the ALTER
- Preserves permissions from the existing object definition to the updated object definition.
The one main downside to this approach is that if the ALTER fails due to a compile issue and the object did not pre-exist before that point, you’re left with a stub that is somewhat meaningless in your database. So, while it’s flexible and removes many of the downsides of the other approaches, it’s was still not perfect.
CREATE OR ALTER
AS of SQL Server 2016 SP1, there’s a new option available that will replace the above solutions as the best option and will work after EXECUTE() is no longer supported as a SQL Server command in future versions of SQL Server. For SQL Server 2016 SP1 and beyond, CREATE OR ALTER PROCEDURE is the way to go. This effectively does the same thing as the Conditionally CREATE then ALTER approach above without creating a temporary stub and will CREATE the object if it doesn’t exist or ALTER the object (without affecting permissions or extensions) if it already exists. This is the win we have been waiting for and will be the standard approach to scripting objects in the future.
Example:
|
1 2 |
CREATE OR ALTER PROCEDURE dbo.spr_SprocName AS SELECT * FROM dbo.TableName |
Advantages of CREATE OR ALTER:
- Allows you to maintain one script definition for your T-SQL objects (Stored Procedures, Views, Functions and Triggers.)
- Maintains permissions if the object already exists.
- Does not replace or DROP the object if the new object definition does not compile, leaving your previous version intact.
- Simplifies logic to Conditionally Create or Alter so you no longer need the hacked approach.
- Will work in future versions of SQL Server that no longer support the EXECUTE() statement.
The only disadvantage of CREATE OR ALTER is that it does not work in SQL Server versions prior to SQL Server 2016 SP1.