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
- Stored procedures
- User-defined functions
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
1 thought on “CREATE OR ALTER – New enhancement in SQL Server 2016 SP1”