CREATE OR ALTER now in SQL 2016 SP1

The other day I wrote about the DROP IF EXISTS statement. I concluded that post by saying that I wished that there was a CREATE OR ALTER statement available and it turns out that there is. As of SQL 2016  SP1 the  CREATE OR ALTER statement is available for a limited number of SQL objects, namely objects that do not have a state.

  • Views
  • Stored Procedures
  • Functions
  • Triggers

An example of using the CREATE OR ALTER statement without the need to check for its existence first.


CREATE OR ALTER PROCEDURE [DBO].[TestProcedure]
AS
BEGIN
SELECT 1 [TestValue]
END
GO

EXEC [DBO].[TestProcedure];
GO

CREATE OR ALTER PROCEDURE [DBO].[TestProcedure]
AS
BEGIN
SELECT 2 [TestValue]
END
GO

EXEC [DBO].[TestProcedure];

It is important to mention that security of these objects is preserved so this is not the same as dropping and recreating an object.

Like the DROP IF EXISTS statement, this will make creating deployable code much easier as we no longer have to perform the existence checks that we had to in the past.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s