DROP IF EXISTS: A Cool Feature in SQL Server 2016
This article will cover a timesaver for those using SQL Server. Need to drop a table? A single statement can handle that without gumming up the works if it's not there.
Join the DZone community and get the full member experience.Join For Free
In this article, we're going over a cool feature which was introduced in SQL Server 2016. That feature is DROP IF EXISTS (DIE) .
During development, we often need to drop a table. As a best practice, we generally use the syntax below.
Now, in SQL Server 2016 the same task is super easy. You can use the following syntax to drop the table object.
DROP TABLE IF EXISTS <TABLE NAME>
And the best part? Suppose the object does not exist. That's a problem, right? Nope. Remember "IF EXISTS?" In that event, there will be no error, and the execution will continue.
Let me share one more example of dropping a stored procedure.
DROP PROCEDURE IF EXISTS <PROCEDURE NAME>
But Wait, There's More!
Similarly, we can handle the following data objects with the same syntax.
DROP ASSEMBLY IF EXISTS Assembly Name
DROP ROLE IF EXISTS ROLENAME
DROP TRIGGER IF EXISTS Trigger Name
DROP VIEW IF EXISTS View Name
DROP RULE IF EXISTS RULENAME
DROP TYPE IF EXISTS Type Name
DROP DATABASE IF EXISTS Database Name
DROP SCHEMA IF EXISTS Schema Name
DROP USER IF EXISTS Username
DROP SECURITY POLICY IF EXISTS Policy Name
DROP FUNCTION IF EXISTS Function Name
DROP SEQUENCE IF EXISTS Sequence Name
DROP INDEX IF EXISTS Index Name ON Table Name
DROP SYNONYM IF EXISTS Synonym Name
I like this feature, and I'm sure you will, too. Please share your feedback, and enjoy!
Published at DZone with permission of Rajat Jaiswal, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.