SQL Server 2016 syntax for dropping objects

The new syntax in SQL Server 2016 for dropping a table if it already exists

SQL Server 2016 introduced a new syntax for dropping objects, that I've known about for quite a while. I've been surprised by the number of people that aren't familiar with it so thought I'd put together a quick post to show it off.

The "traditional" syntax that you'd use when dropping an already existing object and re-creating it is usually something along these lines:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'User')
     DROP TABLE dbo.[User]


CREATE TABLE dbo.[User] (IdColumn INT IDENTITY(1, 1))


SQL Server 2016 introduced a specific language construct for dropping an existing object, IIF it already exists:


CREATE TABLE dbo.[User] (IdColumn INT IDENTITY(1, 1))

The advantage this syntax provides over the longer/older way of doing it is that it avoids situations where copy & paste coding occurs and the name of the object in the check differs from the name of the object in the DROP. With the lack of a C#-esque nameof operator to use for injecting the name of the object in a "strong" way, having this explicit syntax just works. Even if there was a nameof style operator, the much shorter syntax provided by DROP TABLE IF EXISTS would be the preferred way forward.

According to the MSDN SQL Database Engine Blog from way back in Novemeber 2015, the list of object types the syntax (referred to by the charming acronym DIE) can be used with is quite extensive and also goes as far as columns on tables!

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment