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 FreeIn 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.
Assembly
DROP ASSEMBLY IF EXISTS Assembly Name
Role
DROP ROLE IF EXISTS ROLENAME
Trigger
DROP TRIGGER IF EXISTS Trigger Name
View
DROP VIEW IF EXISTS View Name
Rule
DROP RULE IF EXISTS RULENAME
Type
DROP TYPE IF EXISTS Type Name
Database
DROP DATABASE IF EXISTS Database Name
Schema
DROP SCHEMA IF EXISTS Schema Name
User
DROP USER IF EXISTS Username
Security Policy
DROP SECURITY POLICY IF EXISTS Policy Name
Function
DROP FUNCTION IF EXISTS Function Name
Sequence
DROP SEQUENCE IF EXISTS Sequence Name
Index
DROP INDEX IF EXISTS Index Name ON Table Name
Synonym
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.
Comments