DROP IF EXISTS in SQL Server 2016

Introduction

With this very small but handy tip, we’ll check the enhancement that has been made to DROP statement in SQL Server 2016. Earlier, we used to write additional checking logic to make sure that the object exists to drop. If we miss writing the check and if the object is not available, then we are being served with an error message. With SQL Server 2016, now we can check the existence of the respective object without any error being generated.

Need of Checking

The obvious question that may come to mind of those who haven't used DROP statement much is- "Do I really need to check if the object exists prior to executing the DROP statement?"
Well, the answer is a big YES.
Why? Let's quickly test that and see what we get in the result. Execute one of the following statements:

--(1)
DROP Database ARandomDatabaseForDemo
GO

--(2)
DROP TABLE ARandomTableForDemo
GO

--(3)
DROP PROCEDURE ARandomProcForDemo
GO

--(4)
DROP FUNCTION ARandomFunctionForDemo
GO

--(5)
DROP TRIGGER ARandomTriggerForDemo
GO

--Note: Execute only if you don't have the respective objects

It will throw an exception right away as follows:

Now in another case, let's say we have a table but not the column or any attribute/constraint we want to drop.

ALTER TABLE [dbo].[Registration] DROP COLUMN AnyColumn  
GO

We'll get an error message like:

In most of the cases, we may not want to have the error message thrown at us. We would rather like to skip the error message and move forward to execute the rest of the logic in the script. The very common example is when we want to create a table in the database, we generally do a check for if the table exists in that particular database or not and if it exists, then we go ahead to drop it and create the table with the latest structure. This kind of situation generally appears while we are in the initial phase of development or designing our database and multiple resources working on the same project.

The Old Way

Earlier, we were writing a long IF EXISTS statement to check if the respective object exists or not, followed by the DROP statement.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = _
OBJECT_ID(N'[dbo].[ARandomTableForDemo]') AND type in (N'U'))
DROP TABLE [dbo].[ARandomTableForDemo]
GO

Or:

IF OBJECT_ID('[dbo].[ARandomTableForDemo]', 'U') IS NOT NULL
DROP TABLE [dbo].[ARandomTableForDemo]
GO

The New, Easier & Better Way

Let's rewrite the DROP statement with the newer syntax.

Syntax:

DROP DATABASE [ IF EXISTS ] database_name

Example:

DROP DATABASE IF EXISTS ARandomDatabaseForDemo
GO

Isn't it much straight and easy?

Let's look at the complete list of DROP statements which we have written without any check for existence , by applying the new method of DROP IF EXISTS.

--(1) 
DROP Database IF EXISTS ARandomDatabaseForDemo
GO

--(2) 
DROP TABLE IF EXISTS ARandomTableForDemo
GO

--(3) 
DROP PROCEDURE IF EXISTS ARandomProcForDemo
GO

--(4) 
DROP FUNCTION IF EXISTS ARandomFunctionForDemo
GO

--(5) 
DROP TRIGGER IF EXISTS ARandomTriggerForDemo
GO 

And if you want to DROP a column or constraint, you can do so like:

ALTER TABLE [dbo].[Registration] DROP COLUMN IF EXISTS AnyColumn GO

Comparison With Other Competitors

MySQL: MySQL already provides such feature to include this optional clause in the DROP statement.

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

Reference: http://dev.mysql.com/doc/refman/5.7/en/drop-table.html

PostgreSQL: PostgreSQL too already has the same feature.

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Reference: https://www.postgresql.org/docs/8.2/static/sql-droptable.html

Oracle: No such option is available so far. I have checked the documentation and could not find any such mention. So, the option is to either:

• Check the existence of the object prior to DROP
• Catch the exception and handle

Conclusion

This is really a great inclusion to the feature list in Data Definition Language (DDL) for SQL Server 2016. If you found this tip helpful, please share your feedback.

History

• 2^nd October, 2016: First published