DROP IF EXISTS - new thing in SQL Server 2016

In SQL Server 2016 CTP3 objects can DIE (DROP IF EXISTS)

Do you like to write following conditional DROP statements:

 IF OBJECT_ID('dbo.Product, 'U') IS NOT NULL
 DROP TABLE dbo.Product;
 
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trProductInsert')
 DROP TRIGGER trProductInsert

I don't like these, and if you also don't like them, then you might try new DROP IF EXISTS (a.k.a. DIE :) ) statements in SQL Server 2016.

From SQL Server 2016 CTP3 you can use new DIE  statements instead of big IF wrappers, e.g.:

 DROP TABLE IF EXISTS dbo.Product
 
DROP TRIGGER IF EXISTS trProductInsert

 If the object does not exists, DIE will not fail and execution will continue. Currently, the following objects can DIE:

DIE is added on columns and constraints in ALTER TABLE statement

• ALTER TABLE DROP COLUMN IF EXISTS
• ALTER TABLE DROP CONSTRAINT IF EXISTS

Documentation is already published on MSDN:

DROP TABLE (Transact-SQL),DROP PROCEDURE (Transact-SQL), DROP TRIGGER (Transact-SQL), ALTER TABLE (Transact-SQL), etc.

• Anonymous
  November 02, 2015
  Finally! I can remember back to 1999-2000 and Oracle 8, with the statement: CREATE OR REPLACE VIEW...

• Anonymous
  November 03, 2015
  Funny, this afternoon I noticed this posibility on books online and now it announced here. Seems pretty useful and straightforward option.

• Anonymous
  November 03, 2015
  When is MS going to do the same on data change? Something like "insert if not exists"

• Anonymous
  November 03, 2015
  You had me sold on 2016 at JSON support, but this is just as awesome!

• Anonymous
  November 03, 2015
  Hi AL What is the difference between "insert if not exist" and MERGE statement (msdn.microsoft.com/.../bb510625.aspx)? Regards, Jovan

  • Anonymous
    September 04, 2016
    There is no "insert if not exist" in SQL 2016. There's insert .... where not exists ().Don't get the two confused with this article.

• Anonymous
  November 03, 2015
  <i>What is the difference between "insert if not exist" and MERGE statement</i> Simplicity? What would be the MERGE statement equivalent? If I'm not mistaken, the MERGE would be much more verbose and require you to specify the columns in the primary key. You could ask the same question of what's the difference between "CREATE IF NOT EXIST" and "IF OBJECT_ID('dbo.Product, 'U') IS NOT NULL" then...

• Anonymous
  November 03, 2015
  Will alter (procedure, trigger, view) also create the object if it doesn't exist, or will that still require separate check?

• Anonymous
  November 03, 2015
  Hi Joe, You are right, MERGE is created to satisfy SQL Standard and to cover all possible matching scenarios. and it has too many options for the simplest cases. Could you please create or vote for this change on MS connect? CREATE OR REPLACE and DROP IF EXISTS are highly voted items on Ms connect and this is the place where we are  picking changes that should be added. If you get a lot of votes for this item we will include it. Thanks, Jovan

• Anonymous
  November 04, 2015
  The comment has been removed

• Anonymous
  November 04, 2015
  The comment has been removed

• Anonymous
  November 06, 2015
  Well that will save a lot of typos :-) Thanks for publishing was looking forward for this one!

• Anonymous
  November 07, 2015
  This is great!!! Really helpful. You still might want a CREATE OR REPLACE, or a REPLACE, which keeps permissions, FKs, indexes, etc.

• Anonymous
  November 19, 2015
  Pretty useful and similar for data scenarios would be even more beneficial.

• Anonymous
  December 28, 2015
  Awesome

• Anonymous
  December 29, 2015
  Very Nice !!!

• Anonymous
  December 30, 2015
  Great !! for sure I am gonna love MSSQL 2016 !! And thanks to you my co workers will if i use these statements hear me scream DIE ! DIE!  ;-) LOL never heard before of this acronym ...

• Anonymous
  March 30, 2016
  Yeah, I've been asking for Create or Replace for SPs for probably about 10yrs. I don't think it's going to happen anytime soon.

• Anonymous
  August 23, 2016
  Noiiceee!! I likes what I sees.

• Anonymous
  September 06, 2016
  The comment has been removed

  • Anonymous
    September 07, 2016
    What is the version of SQL Server 2016 (select @@version)? Make sure that you are working on CTP3 or later (it is not supported in older CTP version of SQL server 2016)
    • Anonymous
      December 30, 2016
      The comment has been removed

• Anonymous
  December 16, 2016
  Warning: 'dbo.Product --> here a closing quote is missing

• Anonymous
  January 22, 2017
  Thanks for posting this article. its really helpful for me. OBJECT_ID() function is nice solution to find existing object in sql server. I saved this page as bookmark.Thanks Again :)

• Anonymous
  February 07, 2017
  This function does not work on temporal tables. In order to drop a temporal table (and therefor the history table) first row_versioning needs to be switched to off. The both tables need to be dropped, which brings us back to die not being that useful here.Another thing is, what if you only want (for example) a table to be created, if it does NOT exist? Than DIE is also not useful either. Adding the a feature called CNE (create if no Exists) would be usefull as well.

• Anonymous
  February 28, 2018
  IF OBJECT_ID('dbo.Product, 'U') IS NOT NULL doesn't have an apostrophe after dbo.Product. So it should be: IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL.