DROP IF EXISTS- SQL Server 2016 TSQL Enhancements

Though there are couple of additions with SQL Server 2016 on TSQL, had no chance to test them and see. While I was going through the list, the one I found, which is simple but very useful is, DROP IF EXISTS. This allows us to stop writing a lengthy and ugly statement that was used for checking whether the object is exist and then dropping. It basically simplifies the statement.

This can be used against many different objects such as DATABASE, TABLE, VIEW, INDEX, PROCEDURE, TRIGGER, etc. 

Here are some sample codes;

-- creating a database
USE master;
GO

-- Using with database
DROP DATABASE IF EXISTS Sales;
GO

CREATE DATABASE Sales;
GO

USE Sales;
GO

CREATE TABLE dbo.Customer (Id int PRIMARY KEY, Name varchar(100));
CREATE TABLE dbo.Employee (Id int PRIMARY KEY, Name varchar(100));
CREATE TABLE dbo.Product (Id int PRIMARY KEY, Name varchar(100));
CREATE TABLE dbo.Region (Id int PRIMARY KEY, Name varchar(100));

-- Dropping customer table
-- Old method I
--IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Customer')
-- DROP TABLE dbo.Customer;

---- Old method II
--IF OBJECT_ID('dbo.Customer') IS NOT NULL
-- DROP TABLE dbo.Customer;

-- New method
DROP TABLE IF EXISTS dbo.Customer;

-- This will remove Name column if it exist
-- Note that this code can be run without errors
-- even after removing the column
ALTER TABLE dbo.Employee DROP COLUMN IF EXISTS Name;

-- We can drop multiple items like this
DROP TABLE dbo.Product, dbo.Region;

-- Recreating them
CREATE TABLE dbo.Product (Id int PRIMARY KEY, Name varchar(100));
CREATE TABLE dbo.Region (Id int PRIMARY KEY, Name varchar(100));

-- And same can be done with new way too
DROP TABLE IF EXISTS dbo.Product, dbo.Region;