Monthly Archives: August 2016

Speaking at SQLSaturday Oslo

For Valhalla!!

I was very pleased to recently be chosen to speak at SQLSaturday Oslo on the 3rd of September. I spoke there last year and had a great time. The organisers of the event are really friendly and took us on a great trip up to the huge ski jumping location just outside town. I’m looking forward to what they have in store for us this year!

I will be presenting my session “You don’t need Enterprise Edition for do that!” a fun session showing that you don’t necessarily need Enterprise Edition to achieve some of its features.

Just the other day I was also asked if I could fill in a last minute cancellation, so will be delivering a second session “SQL Server 2016, is it worth the Upgrade?“, taking a look at the new features and whether they are compelling enough to make you want to upgrade.

If you are in the Oslo area and haven’t signed up for the event, do it! Going on last year’s success, this year will be well worth a visit! Check out the jam-packed agenda

See you there!

Database objects can DIE in SQL 2016 without using the KILL command

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:


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:


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:


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.