CREATE OR ALTER statement in SQL Server

By:   |   Updated: 2022-12-19   |   Comments (8)   |   Related: More > TSQL


Problem

I am familiar with the T-SQL CREATE statement that is used to create different database objects and the ALTER statement that performs changes on existing database objects. 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

In SQL Server 2016, a new T-SQL statement CREATE OR ALTER was introduced and works with SQL Server 2016 and later. This combines both the CREATE and ALTER statements functionality. So, if the object does not exist already it will be created and if the object does already 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.

With this new CREATE OR ALTER statement, you do not need to add extra code to your script to check if the object exists in the SYSOBJECTS system table and then drop and re-create. 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.

The CREATE OR ALTER 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

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

SQL error message

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, as it will work as an ALTER statement if the object already exists or as a CREATE statement if the object does not already 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, but it does work 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, some 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





get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips


Article Last Updated: 2022-12-19

Comments For This Article




Monday, March 27, 2023 - 5:44:47 PM - Brian Link Back To Top (91056)
We've just started using this syntax, and it does save some coding. However, the context we most often use it in is making weekly fixes to keep two similar databases in sync. If we get a large script from our business partner, we create rollback scripts by scripting the changing sprocs as "ALTER", so if something doesn't work, we can run those scripts to change the database to its previous state.

With SQL Server making the decision on whether to create or alter, it's not obvious which scripts are going to be altered, so we have to go through each sproc individually to see if it exists, and then script an alter for rollback. Is there any way for SQL to tell us which operation it's done on an individual sproc?

Tuesday, January 12, 2021 - 5:38:45 AM - Marcus Neilson Back To Top (88034)
interesting thing worth noting is that when i run "CREATE OR ALTER" on a stored proc I'm replicating, the proc alterations don't get picked up by replication. If you just use "ALTER" to update a stored proc then replication picks up the changes and distributes them.

Thursday, April 2, 2020 - 8:33:09 AM - Abdul Karim Siddiqui Back To Top (85252)

Hello Mr. Ahmad Yaseen,

This is a good feature introduced in this version, but I personally feel it will create a problem in such organization where freshers or juniors have permission to create objects. Earlier if one user creates a SP with the existing object name then system gives an error message and he can change the new object name, but now system will execute it everytime using this new "Create Or Alter" feature.

So, user should be very carefull while using this new feature.


Monday, September 25, 2017 - 10:04:38 AM - Ahmad Yaseen Back To Top (66591)

Unfortunatly not allowed for tables creation.

 

Best Regards, Ahmad

 


Monday, September 25, 2017 - 8:51:49 AM - Nikhil Kulshrestha Back To Top (66588)

 Can we use create or alter when I'm creating a table?

 


Tuesday, February 21, 2017 - 2:48:21 AM - Ahmad Yaseen Back To Top (46538)

 Thank you Lanex for your input here. you are right, no access will be granted if the SP will be created at the first time. So it is better to add the access grant regardless.

 

Best Regards,

Ahmad

 


Monday, February 20, 2017 - 8:58:05 AM - Lanec Rose Back To Top (46523)

Does this feature handle granting of rights? If I create, I need to grant rights for a Proc. If I alter, the existing rights remain. Should I just add a grant regardless?

 


Monday, February 20, 2017 - 4:58:40 AM - shafiullah Back To Top (46512)

Thanks.. nice information and clear cut presentation. 

 















get free sql tips
agree to terms