When developing database code for SQL Server one issue that has got in the way of smooth deployment has been a simple check for the existence of an object before creation.
The issue being, whenever an object should be created, we need to be sure that there isn’t an object already in the database that has the same name. If there is, we need to do something beforehand otherwise the CREATE command is going to fail like this:
Msg 2714, Level 16, State 6, Line 16
There is already an object named {objectname} in the database.
DIE Database Object, DIE!
Up until SQL Server 2016 the only way of making sure an object wasn’t already present was by adding a snippet of code directly before the CREATE statement. This could be achieved in multiple ways, but essentially all solutions just checked for an object with the name provided.
Something along the lines of this:
IF OBJECT_ID('MyTable') IS NOT NULL
DROP TABLE MyTable
While that is a very short piece of code, it is still code that needs to be typed and checked and tested. Of special note is the fact that the object name needs to be correctly typed twice. Let us also not forget, because there are multiple ways of achieving this existence check a new developer may not immediately understand what this code is doing.
You may notice in the paragraph above the code example, I wrote “Up until SQL Server 2016….”. That is because with the release of SQL Server 2016, Microsoft has made our lives a little bit easier in this respect. It is now possible to do the existence check and the drop in one command rather than two.
The long-winded and more error prone example above simply becomes:
DROP TABLE IF EXISTS MyTable
Wow! Drop Table If Exists, or DIE for short. Super-short (only one chance of mistyping the object name) and super easy for anyone to understand, right?
But wait! There’s more!
This existence check is not limited to tables. It covers many more objects, as outlined on the SQL Server Database Engine Blog.
But wait! There’s even more!
If you wanted to be really efficient and only want to write the (not really) verbose DROP TABLE IF EXISTS once for all the tables that you want to drop, you can!
This code will work flawlessly:
DROP TABLE IF EXISTS MyTable, MyOtherTable, YetAnotherTable
The elegance of DROP TABLE IF EXISTS is that if any (or all) of the three tables above exist, they will be deleted. If none of them exist, then nothing will happen.
And here is the catch
Unfortunately, the pluralised usage of DROP TABLE IF EXISTS doesn’t seem to work for all object types. I tried to do the same thing with database users:
DROP USER IF EXISTS User1, User2
This would end in a syntax error.
So there we have it. Objects can now DIE inside SQL Server, without the KILL command
At the time of writing I have not checked all of the supported objects of DIE, but will update this post once I have found the time to do so.
