CREATE OR ALTER statement in SQL Server

Problem

I noticed that SQL Server now has a CREATE OR ALTER T-SQL statement. Can you explain what this does and how to use?

Solution

The CREATE OR ALTER statement was introduced with SQL Server 2016 and works with versions 2016 and later. This combines both the CREATE and ALTER statements functionality. If the object does not exist it will be created and if the object does exist it will be altered/updated. The CREATE OR ALTER statement works with specific types of database objects such as stored procedures, functions, triggers and views.

You do not need to add extra code to check if the object exists in the SYSOBJECTS system table and then drop and re-create the object. The CREATE OR ALTER statement will do that for you. This allows you to streamline your code and eliminate having to write additional code to check for an objects existence.

This statement acts like a normal CREATE statement by creating the database object if the database object does not exist and works like a normal ALTER statement if the database object already exists.

Assume we tried to create a stored procedure that already exists as follows.

USE MSSQLTipsDemo 
GO
CREATE PROC CreateOrAlterDemo 
AS
BEGIN
   SELECT TOP 10 * FROM [dbo].[CountryInfoNew]
END
GO

SQL Server will prevent us from creating the stored procedure since there is a database object with that name already.

We can modify the code to use the CREATE OR ALTER statement instead of just the CREATE statement.  The query execution will succeed each time you run that query. It will work as an ALTER statement if the object exists or as a CREATE statement if the object does not exist.

USE MSSQLTipsDemo 
GO
CREATE OR ALTER PROC CreateOrAlterDemo 
AS
BEGIN
   SELECT TOP 10 * FROM [dbo].[CountryInfoNew]
END
GO

Notes

The CREATE OR ALTER statement does not work for tables and indexes only for stored procedures, functions, triggers and views.

Although this statement is handy to allow you to create a new object or alter an existing object, caution should be used. If for some reason you end up using the same name as an existing object, the alter component will replace the existing code, so be careful how and when you use this.

When using CREATE OR ALTER, SQL Server will check to make sure the object types are compatible, so you don’t accidently replace an existing object with a different object type. So, if we had an existing stored procedure named “dbo.spTest” and tried to use CREATE or ALTER with function code using the same object name “dbo.spTest” we would get the following error:

Msg 2010, Level 16, State 1, Procedure spTest, Line 1 [Batch Start Line 5]
Cannot perform alter on 'dbo.spTest' because it is an incompatible object type.

Next Steps

• Check out this tutorial to learn more about SQL Server Stored Procedures
• CREATE PROCEDURE (Transact-SQL)