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.

 

Before SQL Server 2016 if you wanted to drop an object such as a table you would first have to check if that object existed, then if it did exist delete it. The code could look something like this.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE TABLE_NAME = 'MyTable' AND TABLE_SCHEMA = 'DBO')

DROP TABLE [dbo].[MyTable];

With the enhancement, the code above could be reduced to.

DROP TABLE IF EXISTS [dbo].[MyTable];

This code is both simpler to read and implement which is never a bad thing. Tables are not the only things that can be dropped in this way and other objects such as indexes, schema and even databases can also be dropped in this way.

In addition to dropping the objects,  you can also use the DROP IF EXIST in the ALTER TABLE statement to drop columns or constraints.

DROP TABLE IF EXISTS [dbo].[MyTable];

CREATE TABLE [dbo].[MyTable]

(

[ID] INT,

[SomeColumn] INT,

CONSTRAINT PK_MyTable_ID PRIMARY KEY CLUSTERED ([ID])

);

ALTER TABLE [dbo].[MyTable] DROP COLUMN IF EXISTS [SomeColumn];

ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT IF EXISTS [PK_MyTable_ID];

This is a great addition to T-SQL however, I feel a CREATE OR ALTER IF EXISTS would be more useful although understandably harder to implement and is something we will hopefully see in the near future.