Problem Statement –
Currently one of the most irritating things in SQL, is to explicitly check if an object exists before dropping it.
While writing a TSQL script/stored procedure, there has been multiple times when I try to drop a column/table/trigger/index/user, and get the dreaded error message –
Invalid object name ‘object_name’
Does this error message look familiar to you?
New syntax in SQL Server 2016 –
As part of the TSQL Enhancements, SQL Server 2016 introduces a new clause –
DROP IF EXISTS which conditionally drops the column or constraint only if it already exists.
If the object does not exist, it will not throw any error and the TSQL execution will continue on.
DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME
This syntax looks very simple and clean.
This syntax applies to – Database, Table, Function, Trigger, Stored Procedure, Column, User, View, Schema, Index , Role
Lets look at a demonstration for this new syntax —
First lets create a test table and insert few records into it –
Before SQL Server 2016, we always had to explicitly check if the object exists, and then drop it. There are couple of ways in which we can do this. However with the arrival of SQL Server 2016, we can very easily perform this operation with a simple syntax –
We can also leverage this new statement to our DML queries, to drop columns/constraints, as required. See below —
I have uploaded the entire SQL Script, used for the purpose of this article, in this link in GITHUB
The DROP IF EXISTS is a simple T-SQL Enhancement in SQL Server 2016, but I am sure it would be one of the most frequently used syntax in a T-SQL developer’s day to day life.
Related articles on SQL Server 2016 –
- Dynamic Data Masking in SQL Server 2016
- Time Travel in SQL Server 2016 with Temporal Tables
- Built-in JSON Support in SQL Server 2016
- Compare Execution Plans in SQL Server 2016
- Query Store in SQL Server 2016
- Live Query Statistics in SQL Server 2016