DROP IF EXISTS – new thing in SQL Server 2016

In SQL Server 2016 CTP3 objects can DIE (DROP IF EXISTS)

Do you like to write following conditional DROP statements:

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

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trProductInsert')
DROP TRIGGER trProductInsert

I don’t like these, and if you also don’t like them, then you might try new DROP IF EXISTS (a.k.a. DIE πŸ™‚ ) statements in SQL Server 2016.

From SQL Server 2016 CTP3 you can use new DIE  statements instead of big IF wrappers, e.g.:

DROP TABLE IF EXISTS dbo.Product

DROP TRIGGER IF EXISTS trProductInsert

 If the object does not exists, DIE will not fail and execution will continue. Currently, the following objects can DIE:

AGGREGATE

PROCEDURE

TABLE

ASSEMBLY

ROLE

TRIGGER

VIEW

RULE

TYPE

DATABASE

SCHEMA

USER

DEFAULT

SECURITY POLICY

VIEW

FUNCTION

SEQUENCE

 

INDEX

SYNONYM

 

DIE is added on columns and constraints in ALTER TABLE statement

  • ALTER TABLE DROP COLUMN IF EXISTS
  • ALTER TABLE DROP CONSTRAINT IF EXISTS

Documentation is already published on MSDN:

DROP TABLE (Transact-SQL),DROP PROCEDURE (Transact-SQL)DROP TRIGGER (Transact-SQL)ALTER TABLE (Transact-SQL), etc.