CREATE OR ALTER – In SQL Server 2016..A Brand New Syntax :)

From SQL Server 2016 onward, you don't have to check for the existence of the object with the help of IF…ELSE… conditional statement and decide to execute either the CREATE or ALTER statement in the respective code block. Because, Microsoft introduces a New syntax that will help us to avoid the additional pain and also save time in writing all these logic.

This new statement combines CREATE and ALTER statements and creates object if it does not exist, or alter it if it is already there. 

CREATE OR ALTER can be applied on the following object:

STORED PROCEDURES
FUNCTIONS
TRIGGERS
VIEWS

Syntax:

CREATE OR ALTER object_name

Now let's see the same with an example. Suppose we have a task where we want to modify an object i.e. Stored Procedure and we are not sure if it already exists on the instance. Then earlier we were writing code as below:


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]'))  
    EXEC sp_executesql   
        N'CREATE PROCEDURE [dbo].[Test]  
        AS  
        BEGIN  
            SET NOCOUNT ON;  
            PRINT ''Hello Readers'' 
        END'  
ELSE  
    EXEC sp_executesql   
        N'ALTER PROCEDURE [dbo].[Test]  
        AS  
        BEGIN  
            SET NOCOUNT ON;  
            PRINT ''Hello Readers''  
        END'    

Now let's see how can we replace the above code with the help of new syntax:

CREATE OR ALTER PROCEDURE [dbo].[Test]  
AS  
BEGIN  
    SET NOCOUNT ON;  
    PRINT 'Hello Readers'  
END 


It looks like this statement is going to help us a lot.

Thank You!