SQL Server 2016 Service Pack 1 introduced several enhancements that greatly improve productivity for developers. One of these enhancements is the introduction of the CREATE OR ALTER statement, which allows for easier deployment of scripts to SQL Server.
Have you ever encountered a situation where you needed to deploy a stored procedure to SQL Server, but you were unsure if it had already been deployed? In such cases, developers would typically need to check if the stored procedure exists, drop it if it does, and then create it again. This process can be time-consuming and error-prone.
Oracle has had the CREATE OR REPLACE statement for a long time, which allows developers to create or replace an object in a single statement. This feature has been highly requested by the SQL Server community, and Microsoft has finally addressed this need in Service Pack 1 of SQL Server 2016.
The CREATE OR ALTER statement can be used with the following objects:
- Stored procedures
- Triggers
- User-defined functions
- Views
Unfortunately, this feature does not work for objects that require storage changes, such as tables and indexes. It is also not allowed for CLR procedures.
Here is an example of how the CREATE OR ALTER statement can be used to create or alter a stored procedure:
CREATE OR ALTER PROCEDURE usp_SQLAuthority AS BEGIN SELECT 'ONE' END
This enhancement greatly simplifies the deployment process for developers. They no longer need to manually check if an object exists and then drop and create it accordingly. Instead, they can use the CREATE OR ALTER statement to handle both scenarios in a single statement.
If you are interested in learning more about this feature, I highly recommend reading the blog post “SQL Server – 2016 – T-SQL Enhancement ‘Drop if Exists’ clause”. It provides additional insights and examples of how this enhancement can be used.
Were you aware of this enhancement in SQL Server 2016 Service Pack 1? Let me know if you would like me to write more about the other enhancements introduced in this service pack.