In Sql Server 2016, IF EXISTS is the new optional clause introduced in the existing DROP statement. Basically, it checks the existence of the object, if the object does exists it drops it and if it doesn’t exists it will continue executing the next statement in the batch. Basically it avoids writing if condition and within if condition writing a statement to check the existence of the object.

Syntax:

DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

WHERE: OBJECT_TYPE like Table, Procedure, View, Function, Database, Trigger, Assembly, Sequence, Index etc.

IF EXISTS: It is an optional clause and if it is mentioned in the DROP statement then it checks the existence of the object, if it exists it will drop otherwise continues executing the next statement in the block without raising any issues.

Let us understand this new IF EXISTS clause in the DROP statement with an extensive list of examples. To demonstrate this clause let us first create a sample Database with a Table and a Stored Procedure.

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
CREATE TABLE dbo.Customers  (Id INT, Name Nvarchar(50))
GO
CREATE PROCEDURE dbo.WelcomeMessage
AS
	SELECT 'Welcome to Sql Server'
GO

DROP Stored Procedure IF EXISTS

[ALSO READ] How to check if a Stored Procedure exists

In Sql Server 2016 we can write a statement like below to drop a Stored Procedure if exists.

DROP PROCEDURE IF EXISTS dbo.WelcomeMessage

If the stored procedure doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the stored procedure WelcomeMessage which is already dropped.

From the result it is clear that it will not raise any error if the stored procedure doesn’t exists, it will continue executing the next statement in the batch.

In previous versions of Sql Server we use statement like below to drop the stored procedure if exists.

IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'WelcomeMessage')
BEGIN
    DROP PROCEDURE dbo.WelcomeMessage
END

You can go through the article How to check if a Stored Procedure exists to see various options in the previous versions of Sql Server to check for the existence of a Stored Procedure.

DROP TABLE IF EXISTS

[ALSO READ] How to check if a Table exists

In Sql Server 2016 we can write a statement like below to drop a Table if exists.

DROP TABLE IF EXISTS dbo.Customers

If the table doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Table dbo.Customers which is already dropped.

From the result it is clear that it will not raise any error if the Table doesn’t exists, it will continue executing the next statement in the batch.

In previous versions of Sql Server we use statement like below to drop the Table if exists.

IF EXISTS(SELECT 1 FROM sys.Tables 
          WHERE  Name = N'Customers' AND Type = N'U')
BEGIN
  DROP TABLE dbo.Customers
END

You can go through the article How to check if a Table exists to see various options in the previous versions of Sql Server to check for the existence of a Table.

DROP DataBase IF EXISTS

[ALSO READ] How to check if a Database exists

In Sql Server 2016 we can write a statement like below to drop a DataBase if exists.

USE MASTER
GO
DROP DATABASE IF EXISTS SqlHintsDemoDB

If the Database doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Database SqlHintsDemoDB which is already dropped.

From the result it is clear that, it will not raise any error if the Database doesn’t exists, it will continue executing the next statement in the batch.

In previous versions of Sql Server we use statement like below to drop the Database if exists.

IF DB_ID('SqlHintsDemoDB') IS NOT NULL
BEGIN
    DROP DATABASE SqlHintsDemoDB
END

You can go through the article How to check if a Database exists to see various options in the previous versions of Sql Server to check for the existence of a Database.

[ALSO READ]:

• Native JSON Support in Sql Server 2016
  • FOR JSON Clause in Sql Server 2016
  • OPENJSON Function in Sql Server 2016
  • ISJSON Function in Sql Server 2016
  • JSON_VALUE Function in Sql Server 2016
  • JSON_QUERY Function in Sql Server 2016
  • lax and strict JSON Path modes in Sql Server 2016
  • Indexing Strategy for JSON Value in Sql Server 2016
• DROP IF EXISTS Statement in Sql Server 2016
• Compare Execution Plans in Sql Server 2016
• Live Query Statistics in Sql Server 2016
• DATEDIFF_BIG Function in Sql Server 2016
• Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
• SESSION_CONTEXT in Sql Server 2016