SQL Server 2016 Hosting – HostForLIFE.eu :: DROP IF EXISTS In SQL Server 2016

With this very small but handy tip, we’ll check the enhancement that has been made to the DROP statement in SQL Server 2016. Earlier, we used to write additional checking logic to make sure that the object existed 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 the 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.

Note – Execute only if you don’t have the respective objects. It will throw an exception right away, as follows –

Cannot drop the database/table/procedure/function/trigger ‘ARandom[Database/Table/Procedure/Function/Trigger]ForDemo’, because it does not exist or you do not have permission.

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

We’ll get an error message like,

ALTER TABLE DROP COLUMN failed because column ‘AnyColumn’ does not exist in table ‘Registration’.

In most cases, we may not want to have an 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.

Or,

The New, Easier & Better Way

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

Syntax

Example

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.

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

Comparison With Other Competitors

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

PostgreSQL

PostgreSQL too already has the same feature.

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