DROP IF EXISTS Statement in SQL Server 2016

By Samir Behara on May 23, 2016 • ( 2 )

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’

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 –

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 –

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

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 –

‹ Dynamic Data Masking in SQL Server 2016

Troubleshooting random failures with Coded UI Tests ›

Categories: SQL Server, SQL Server 2016

xpil

October 24, 2016 • 4:13 am

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.

LikeLike

Reply ↓