Share via

DROP IF EXISTS - new thing in SQL Server 2016

  • Article

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:

AGGREGATE

PROCEDURE

TABLE

ASSEMBLY

ROLE

TRIGGER

VIEW

RULE

TYPE

DATABASE

SCHEMA

USER

DEFAULT

SECURITY POLICY

VIEW

FUNCTION

SEQUENCE

  

INDEX

SYNONYM

  

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.

Comments

  • 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.