DROP IF EXISTS - new thing in SQL Server 2016

11/03/2015

2 minutes to read

In this article

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.