Advertisements
Home > SQL Server 2016 > New syntax option “IF EXISTS” with DROP and ALTER statements – in SQL Server 2016

New syntax option “IF EXISTS” with DROP and ALTER statements – in SQL Server 2016

November 3, 2015 Leave a comment Go to comments

I was going through some sample Scripts provided by Microsoft SQL Server team on their site, and was checking the JSON Sample Queries procedures views and indexes.sql script file.
 

And the following ALTER statement grabbed my attention:

ALTER TABLE Sales.SalesOrder_json
DROP
	COLUMN IF EXISTS vCustomerName,
	CONSTRAINT IF EXISTS [SalesOrder reasons must be formatted as JSON array],
	COLUMN IF EXISTS SalesReasons,
	CONSTRAINT IF EXISTS [SalesOrder items must be formatted as JSON array],
	COLUMN IF EXISTS OrderItems,
	CONSTRAINT IF EXISTS [SalesOrder additional information must be formatted as JSON],
	COLUMN IF EXISTS Info
GO

The above DDL Query is removing Columns and Constraints form the table, and if you notice there is a new option after the COLUMN/CONSTRAINT name i.e. IF EXISTS.
 

And same with the DROP statements:

DROP INDEX IF EXISTS idx_SalesOrder_json_CustomerName ON Sales.SalesOrder_json
go
DROP PROCEDURE IF EXISTS Person.PersonList_json
go
DROP VIEW IF EXISTS Sales.vwSalesOrderInfoRel_json
go
DROP FUNCTION IF EXISTS dbo.ufnToRawJsonArray
go

Here also it is dropping Database objects conditionally by using IF EXISTS in between the object type and name.
 

To make sure I checked the MSDN BOL and found that this is a new feature added to the SQL Server 2016 version. And as per this msdn article this enhancement has been add with the CTP 3 release.

For IF EXISTS option/syntax the MSDN BoL mentions: Conditionally drops the [object] only if it already exists.
 

This is a very helpful enhancement added to these DDL statements and would reduce a lot of effort and coding lines.

–> Previously with ALTER statement, to DROP any item you had to check the existence of it with a separate IF EXISTS() statement, and then DROP it within the IF condition, like:

IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'vCustomerName')
BEGIN
	ALTER TABLE Sales.SalesOrder_json
	DROP COLUMN vCustomerName;
END
GO

This is only for one column, to DROP other 6 columns/constraints you will have to repeat this 6 more times.

–> Similarly for the DROP statement, you would need to do:

IF EXISTS (select * from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'PersonList_json')
BEGIN
	DROP PROCEDURE Person.PersonList_json
END
GO

But if you check the ALTER statement with the IF EXISTS option at the top it is doing 7 ALTER DROP operations with one statement and within a single transaction. And similarly the DROP statement with this IF EXISTS option is doing it in one go.
 

Thus, the new IF EXISTS syntax can be used optionally to check & drop items form a single statement with these DDLs:

1. ALTER: statement with DROP COLUMN & DROP CONSTRAINT option.

2. DROP: statement with all Database objects, like: AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW.
 

You can download the SQL Server 2016 sample Database and files from this link.
 

Check more new features of SQL Server 2016 here: https://sqlwithmanoj.com/sql-server-2016-articles/


Advertisements
  1. Shubham Kanojia
    April 12, 2018 at 11:00 pm

    Can this also be used for conditionally deleting rows from a table?

  1. March 14, 2017 at 8:48 pm
  2. November 28, 2016 at 12:37 am
  3. November 9, 2015 at 7:43 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: