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: 

AGGREGATE PROCEDURE TABLE
ASSEMBLY ROLE TRIGGER
VIEW RULE TYPE
DATABASE SCHEMA USER
DEFAULT SECURITY POLICY VIEW
FUNCTION SEQUENCE
INDEX SYNONYM

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

Avatar

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published. Required fields are marked *