T-SQL Ninja #29

DROP IF EXISTS

What is DROP IF EXISTS?

You’ve seen it over and over again in the past months, and also from the syntax, the functionality of DROP IF EXISTS is relatively clear: Dropping a database object, if it exists. The syntax for this was introduced in SQL Server 2016 and saves us poor ninjas from some rather unpleasant syntax-climbing in the past.

How can you work with DROP IF EXISTS?

Using DROP IF EXISTS is very easy. In the past, you had constructs that used the sys tables or the OBJECT_IDfunction to check if certain objects existed, today you use DROP IF EXISTS instead. Let’s have a look at this. First we create a database object:

CREATE TABLE #Dojo 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
)

Let us now try to create the object again with a changed configuration:

CREATE TABLE #Dojo 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
    ,Age int 
)

Of course, this attempt fails, because the object names must be unique in the database and the SQL server reports

There is already an object named '#Dojo' in the database.

Before we have to create the object, of course we have to delete the old object.

Previously, before the CREATE statement, we would have checked if the table exists. I usually did this by checking if the `OBJECT_ID’ function returns a value:

IF OBJECT_ID('tempdb..#Dojo') IS NOT NULL DROP TABLE #Dojo;
GO

What was always unpleasant about this solution was that we had to query temporary tables like in our case “tempdb” without a schema (hence the two dots). Nevertheless this way was of course purposeful, because the following statement leads to the fact that we find a #Dojo table with the desired three columns in the database:

IF OBJECT_ID('tempdb..#Dojo') IS NOT NULL DROP TABLE #Dojo;
GO

CREATE TABLE #Dojo 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
)

IF OBJECT_ID('tempdb..#Dojo') IS NOT NULL DROP TABLE #Dojo;
GO

CREATE TABLE #Dojo 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
    ,Age int 
)

Instead of using the OBJECT_ID function, there was also the option to always execute a DROP TABLE as a precaution and simply intercept and ignore the error that occurs when the object does not exist in a TRY CATCH statement:

BEGIN TRY DROP TABLE #Dojo2 END TRY BEGIN CATCH END CATCH
GO

Also here you can confirm the functionality, because after executing the following statement there is again a #Dojo2 table with three columns in the database:

BEGIN TRY DROP TABLE #Dojo2 END TRY BEGIN CATCH END CATCH
GO

CREATE TABLE #Dojo2 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
)

BEGIN TRY DROP TABLE #Dojo2 END TRY BEGIN CATCH END CATCH
GO

CREATE TABLE #Dojo2 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
    ,Age int 
)

Both methods still work – but with the new syntax it’s much more elegant:

DROP TABLE IF EXISTS #Dojo3;
GO 

And also here we can demonstrate the functionality relatively easily:

DROP TABLE IF EXISTS #Dojo3;
GO 

CREATE TABLE #Dojo3 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
)

DROP TABLE IF EXISTS #Dojo3;
GO

CREATE TABLE #Dojo3 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,NinjaName varchar(250)
    ,Age int 
)

select * from #Dojo3

Of course there were other ways to check the existence of objects, for example by querying sys tables:

SELECT name FROM tempdb.sys.objects WHERE name LIKE N'#Dojo[_]%';

or:

SELECT name FROM sys.objects WHERE name = N'DojoPerm';

But all these constructs are just tools that we use to say “delete the object if it exists” and it’s nice that SQL Server now gives us a syntax to say just that.

References

Ninja-Notebooks @ GitHub

Leave a Reply

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