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:

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 anything like 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.

/*It is to be noted that the column drop if exists syntax is different*/
ALTER TABLE tbl_verify DROP column IF EXISTS bbbb

/*Similar way is also applicable for dropping constraints as well*/
ALTER TABLE tbl_verify DROP Constraint IF EXISTS PK__tbl_veri__4EE7470B21966CBA

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

If you enjoyed this blog post, feel free to share it with your friends!