CREATE OR ALTER is one of the new Transact-Sql DDL statement supported in Sql Server 2016 SP1. This statement can be used while creating or altering the Stored Procedures, Functions, Triggers and Views. Basically, if we are using this statement with a module like Stored Procedure, then if the Stored Procedure doesn’t exists it will create it and if it already exists then it will alter the the Stored Procedure. The good thing with this statement is, if the module already existing it alters the module definition but preserves the existing permissions on it.

Let us understand this feature with an example. Execute the following script to create a stored procedure WelcomeMessage and then verify the SP content by executing the SP_HELPTEXT statement.

CREATE OR ALTER PROCEDURE WelcomeMessage
AS
    SELECT 'Welcome to Sqlhints'
GO
SP_HELPTEXT WelcomeMessage

RESULT:

From the above result we can see that the stored procedure WelcomeMessage is created.

Now execute the below statement, which is same as the previous statement and only difference is the change in the WelcomeMessage Stored Procedure definition:

CREATE OR ALTER PROCEDURE WelcomeMessage
AS
    SELECT 'Welcome to WWW.Sqlhints.COM'
GO
SP_HELPTEXT WelcomeMessage

RESULT:

From the result we can see that this time as the WelcomeMessage stored procedure already exists. The CREATE OR ALTER statement modified the stored procedure definition. So CREATE OR ALTER statement creates the module if it doesn’t exists already, if the module already exists it will alter the module.

In the prior versions of Sql Server to achieve this we would have checked the existence of the module first, if it exists we drop the module and then create the module as shown in the below script. The problem with this approach is the re-creation of the Stored procedure requires us to re-grant the permission as the previous permissions assigned to the object are lost when object is dropped. Where as in case of CREATE OR ALTER statement, for an existing object it uses the ALTER statement, in that way the previous permissions granted to the object remains intact and no need to re-grant.

IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'WelcomeMessage')
BEGIN
    DROP PROCEDURE dbo.WelcomeMessage
END
GO
CREATE PROCEDURE WelcomeMessage
AS
    SELECT 'Welcome to WWW.Sqlhints.COM'
GO