SQL Server 2016 syntax for dropping objects

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')
BEGIN
     DROP TABLE dbo.[User]
END

GO

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

GO

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

DROP TABLE IF EXISTS [User]
GO

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!