DIE (DROP IF EXISTS) in SQL 2016

Hello Friends,

Getting back with you my new blog with DIE. 🙂 Sound interesting & crazy at same time. Yes, SQL Server 2016 is getting to us with a new T-SQL clause DROP IF EXISTS, in short (DIE).

Traditionally we all, irrespective of holding developer, DBA or DEVOPS profile very much used to of IF EXISTS statement to check the existence of the object(s), if the object exists following statement starting with “DROP”. SQL Server 2016 has come up with jamming those two different statements together as “DROP IF EXISTS”. Definitely going to help you in squeezing code lines, perhaps not much however good to have feature.

IF EXISTS check the existence of an object and based upon return value (TRUE/FALSE) IT DROPS associated object or not. This statement will avoid writing of condition and within if condition writing a statement to check the existence of an object.

Applies To: 

Let me give example here how it works traditionally vs in SQL Server 2016.

-- Traditional Script
IF OBJECT_ID('[HumanResource].[Department]', 'U') IS NOT NULL
DROP TABLE [HumanResource].[Department]

-- Another way of Scripting to achieve same goal
IF EXISTS(SELECT 1 FROM sys.tables 
          WHERE Name = 'Department')
BEGIN
    DROP TABLE [HumanResource].[Department]
END

in SQL Server 2016

-- DIE in SQL Server 2016
BEGIN
    DROP TABLE IF EXISTS [HumanResource].[Department]
END

Another benefit of DIE clause it can easily jam with ALTER Statement. Here is quick example

ALTER TABLE DIE_TESTING DROP CONSTRAINT IF EXISTS DIE_ID_PK1;
ALTER TABLE DIE_TESTING DROP COLUMN IF EXISTS DIE_NAME;

I’m using this new statement here and there with caution, still verify twice (/perhaps multiple times) before shooting into PRODUCTION environment. 🙂

Happy Learning!

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook