CREATE OR ALTER – New enhancement in SQL Server 2016 SP1

An interesting new enhancement was introduced in SQL Server 2016 SP1. As part of this release, a new T-SQL statement, CREATE OR ALTER was introduced which combines both the CREATE and ALTER statement functionality.

The CREATE OR ALTER statement can be used on below objects

• Views
• Stored procedures
• User-defined functions
• Triggers

Previously you needed to add an IF EXISTS() condition to check if the object exists in the SYSOBJECTS system table. If exists then drop and create a new object.

But using the new CREATE OR ALTER statement, you can avoid checking for if the object already exists or not. 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.

For example, you can simply use the new CREATE OR ALTER option to create a Stored Procedure without checking if it exists or not.

CREATE OR ALTER PROCEDURE dbo.[sp_GetEmployeeDetails]
    @EmpID INT
AS
BEGIN
    SELECT FirstName, MiddleName, LastName 
    FROM EmployeeInfo
    WHERE EmployeeID = @EmpID
END

You can read more about CREATE OR ALTER statement at this link