DROP [IF EXISTS]

What is it?

Microsoft introduced a new method of dropping objects in SQL Server 2016. There are many times we want to conditionally drop objects to prevent errors or to allow scripts to be idempotent. Without first checking if the object exists, and it turns out it doesn’t exist, the DROP statement would fail. DROP IF EXISTS makes this much simpler.

The old way.

Previously, if we wanted to conditionally drop an object (a table, in these examples) we would use statements like the following:

-- 'U' corresponds to the Table (user-defined) object type.
IF OBJECT_ID(N'dbo.Blah', N'U') IS NOT NULL
    DROP TABLE dbo.Blah;

IF EXISTS
(
    SELECT * 
    FROM sys.tables AS T 
    INNER JOIN sys.schemas AS S 
	    ON T.schema_id = S.schema_id 
    WHERE T.name = N'Blah'
        AND S.name = N'dbo'
)
    DROP TABLE dbo.Blah;

IF EXISTS
(
    SELECT * 
    FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = N'Blah'
        AND TABLE_SCHEMA = N'dbo'
)
    DROP TABLE dbo.Blah;

Out of those examples, I would prefer the first one. It’s the shortest and easiest to interpret, in my opinion.

The new way.

If we wanted to replicate that same functionality, we can execute the following:

DROP IF EXISTS dbo.Blah;

This is much simpler. The DROP IF EXISTS statement is also referred to as DIE, mentioned in JovanPop’s DROP IF EXISTS – new thing in SQL Server 2016 article. The article also provides a list of all valid objects that can be used with the DIE method.

SQL Server isn’t the first to introduce this optional syntax, it’s an extension to the ANSI/ISO standard for SQL. I think MySQL was the first RDMBS to introduce this, but I could be wrong about that!

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *