"CREATE OR ALTER" and DIE Commands in SQL Server 2016

 

     CREATE OR ALTER statement came with SQL Server 2016 SP1. It combines the CREATE and ALTER functionality. It creates the object if it doesn't exist, or alters the object if it exists. CREATE OR ALTER supports the following object types.

1. STORED PROCEDURES
2. FUNCTIONS
3. TRIGGERS
4. VIEWS  

 
    Now, Let's create a situation to use this statement in an example. First we are creating a stored procedures by using regular CREATE statement in the following example.

CREATE OR ALTER PROC LoadAllCustomers
AS
BEGIN
SELECT CustomerId, CustomerName FROM [dbo].[Customers]
END
GO

    Two months later, your requirements change and you need to add the STATE column to this stored procedure. To do that, we can use the CREATE OR ALTER statement. In this example, SQL Server alters your stored procedure since it's already exists.

CREATE OR ALTER PROC LoadAllCustomers
AS
BEGIN
SELECT CustomerId, CustomerName, State FROM [dbo].[Customers]
END
GO

   Since we are talking about checking if object exists or not. Let's look at another simple but very useful addition to SQL Server 2016. Probably, you use something very similar to the following example when you need to figure out if a table exists before you drop it.

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'Customers'))
BEGIN
   DROP TABLE dbo.Customers
END dbo.Customers

OR

IF OBJECT_ID('dbo.Customers, 'U') IS NOT NULL
 DROP TABLE dbo.Customers;

    SQL Server 2016 comes with a new syntax doing just that for you DROP IF EXISTS. (Also known as DIE) DIE can drop many other objects rather than tables too.  Here is an example for DROP IF EXISTS syntax for database table.

DROP TABLE IF EXISTS dbo.Customers

When we run this syntax, SQL Server drops the Customers table if it exists. DROP IF EXISTS supports the following objects. You can use DIE to drop them if the object exists.

• AGGREGATE
• ASSEMBLY
• VIEW
• DATABASE
• DEFAULT
• FUNCTION
• INDEX
• PROCEDURE
• ROLE

• RULE
• SCHEMA
• SECURITY POLICY
• TABLE
• TRIGGER
• TYPE
• USER
• VIEW

Also you can use DROP IF EXITS in ALTER TABLE statements to drop columns or constraints. Here is an example.

ALTER TABLE DROP COLUMN colname IF EXISTS
ALTER TABLE DROP CONSTRAINT constname IF EXISTS