DROP IF EXISTS Statement in SQL Server 2016


Problem Statement

Currently one of the most irritating things in SQL, is to explicitly check if an object exists before dropping it.

While writing a TSQL script/stored procedure, there has been multiple times when I try to drop a column/table/trigger/index/user, and get the dreaded error message –
Invalid object name ‘object_name’

InvalidObjectName

Does this error message look familiar to you?

New syntax in SQL Server 2016 –

As part of the TSQL Enhancements, SQL Server 2016 introduces a new clause –

DROP IF EXISTS which conditionally drops the column or constraint only if it already exists.
If the object does not exist, it will not throw any error and the TSQL execution will continue on.

DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

This syntax looks very simple and clean.

This syntax applies to – Database, Table, Function, Trigger, Stored Procedure, Column, User, View, Schema, Index , Role

Lets look at a demonstration for this new syntax

First lets create a test table and insert few records into it –

Create Table

Before SQL Server 2016, we always had to explicitly check if the object exists, and then drop it. There are couple of ways in which we can do this. However with the arrival of SQL Server 2016, we can very easily perform this operation with a simple syntax –

BeforeSyntax

We can also leverage this new statement to our DML queries, to drop columns/constraints, as required. See below —

DropIfExists

I have uploaded the entire SQL Script, used for the purpose of this article, in this link in GITHUB

Conclusion

The DROP IF EXISTS is a simple T-SQL Enhancement in SQL Server 2016, but I am sure it would be one of the most frequently used syntax in a T-SQL developer’s day to day life.

Related articles on SQL Server 2016 –

2 thoughts on “DROP IF EXISTS Statement in SQL Server 2016

  1. I’m a huge fan of MSSQL but this particular one is simply bad. It looks like a work of a drunk Polish plumber. It works for the time being but it will start leaking sooner or later, when least expected.

    The particular scenario I have on my mind here is when permissions are associated with the object being dropped. Once the object is gone, the permissions are gone too and we have to re-create them (or forget to do so and encounter weird errors immediately after).

    Instead, they should have came up with something resembling Oracle’s CREATE OR REPLACE which does not drop an existing object thus preserving it’s permissions.

    I understand they are trying to avoid stepping on 40 years old Giant’s toes. But for god’s sake they should came up with something better than this.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s