10

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

 3 years ago
source link: https://sqlwithmanoj.com/2015/11/03/new-syntax-option-if-exists-with-drop-and-alter-statements-in-sql-server-2016/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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/

Related


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK