SQL 2016: Drop If Exists
Author: jason
Date: 2019-01-17 10:59:08
Category: Technical

A new feature introduced in SQL 2016 is the tsql syntax: DROP IF EXISTS

The following items can be used:AGGREGATE, PROCEDURE, TABLE, ASSEMBLY, ROLE, TRIGGER, VIEW, RULE, TYPE, DATABASE, SCHEMA, USER, DEFAULT, SECURITY POLICY, VIEW, FUNCTION, SEQUENCE, INDEX, SYNONYM

So, while DROP IF EXISTS can be used with Procedures and Views, it might be easier to use CREATE OR ALTER. That's new too.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

https://www.mssqltips.com/sqlservertip/4402/new-drop-if-exists-syntax-in-sql-server-2016/

https://www.mssqltips.com/sqlservertip/4108/tsql-enhancements-introduced-in-sql-server-2016/

Code


/*
Previously when you made a #Temp Table, ran the script,
and then ran the script subsequent times, it would fail
because the table was already created

Now, you can run this multiple times with no fails
*/

--Here is a Table
DROP TABLE IF EXISTS #Temp; --New in SQL 2016, so cool!!
CREATE TABLE #Temp ([Name] VARCHAR(255));

--And now an Index!!
DROP INDEX IF EXISTS Temp_Name ON #Temp;
CREATE NONCLUSTERED INDEX [Temp_Name] ON #Temp ([Name] ASC);







jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009