New syntax option “IF EXISTS” with DROP and ALTER statements – in SQL Server 2016
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/