DROP IF EXISTS – A handy feature in SQL Server 2016

Microsoft has introduced a handy cool feature in SQL Server 2016 called DROP IF EXISTS for objects.

Applies to : SQL Server 2016 and later
This is applicable for dropping an object with existence check. In early versions, we need to write the existence condition and then drop the objects. For an example, if we need to drop a table, we would write code as follows:


Create Table dbo.ExistsCheck(Col1 int)

--Code to check the existence and drop the object
If exists (Select 1 From sys.tables where name='dbo.ExistsCheck')
	Drop table dbo.ExistsCheck

Let us look at the execution plan and the statistics information for the below:

ifexists_old

SQL Server 2016 and later version has a new way to do as follows:


--Syntax
DORP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

      Object_Type can be Table, Procedure, View, Function, Database, Trigger, Assembly(not supported in AZURE), Sequence, Index, Role, user,type,synonym,column.
--Eg:
Drop table If exists dbo.ExistsCheck

Using the above syntax, it is interested to note that no execution plan and no statistics generated as those are trivial operations.

ifexists_new

I personally feel this is a nice feature we can adopt in new development activities(caveat: only for SQL Server 2016 and later versions).

Advertisements

2 thoughts on “DROP IF EXISTS – A handy feature in SQL Server 2016

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