Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


New CREATE OR ALTER statement in SQL Server 2016 SP1

By:   |   Last Updated: 2017-01-23   |   Comments (5)   |   Related Tips: More > SQL Server 2016


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 in SQL Server 2016 there is now a CREATE OR ALTER T-SQL statement. Can you explain what this does and how to use?


In SQL Server 2016 SP1, a new T-SQL statement, CREATE OR ALTER was introduced. This combines both the CREATE and ALTER statements functionality. 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.

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.

CREATE PROC CreateOrAlterDemo 
SELECT TOP 10 * FROM [dbo].[CountryInfoNew]

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.

SELECT TOP 10 * FROM [dbo].[CountryInfoNew]

Note that this new T-SQL statement does not work for tables and indexes.

Next Steps

Last Updated: 2017-01-23

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
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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

Unfortunatly not allowed for tables creation.


Best Regards, Ahmad


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

 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

 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,



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

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

Thanks.. nice information and clear cut presentation. 


Learn more about SQL Server tools