New CREATE OR ALTER statement in SQL Server 2016 SP1
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.
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.
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.
USE MSSQLTipsDemo GO CREATE OR ALTER PROC CreateOrAlterDemo AS BEGIN SELECT TOP 10 * FROM [dbo].[CountryInfoNew] END GO
Note that this new T-SQL statement does not work for tables and indexes.
- Read more about SQL Server Stored Procedure Tutorial
- Check out New Features in SQL Server 2016 Service Pack 1
- Download SQL Server 2016 and learn about this new feature and others
Last Updated: 2017-01-23
About the author
View all my tips