With a conditional DROP statement, you can avoid getting an exception if the object you want to drop does not exist. If, for instance, the T1 table has already been removed or it was not created at all, the following statement will fail:
DROP TABLE dbo.T1;
Here is the error message:
Msg 3701, Level 11, State 5, Line 5 Cannot drop the table 'dbo.T1', because it does not exist or you do not have permission.
SQL Server 2016 introduces the conditional DROP statement for most of the database objects. The conditional DROP statement is a DROP statement extended with the IF EXISTS part. Repeat the preceding command with this extended syntax:
DROP TABLE IF EXISTS dbo.T1;
You can execute this statement any number of times and you will not get an error. To achieve this prior to SQL Server 2016, you had to check the existence of the object before you removed it, as shown in this code example:
IF OBJECT_ID('dbo.T1','U') IS NOT NULL
DROP TABLE dbo.T1;
You had to write one more code line and, in addition, it was also error prone—you had to write the name of the object twice. It's not a big deal, but this new form is shorter and is not error prone.
You can use the following code to remove the stored procedure dbo.P1 from the system:
DROP PROCEDURE IF EXISTS dbo.P1;
As mentioned earlier, you could use the conditional DROP statement in SQL Server 2016 to remove most of the database objects. The following objects are supported: AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW.
If you want, for instance, to remove a partitioned function or schema, DROP IF EXISTS won't work. The following command will fail:
DROP PARTITION FUNCTION IF EXISTS PartFunc1;
And here is the error message:
Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'IF'
To (conditionally) remove a partitioned function, you still need to write your own code to check the existence of the object.
How does IF EXISTS work? It simply suppresses the error message. This is exactly what you need if the reason for the error is the nonexistence of the object. However, if the user who wants to drop the object does not have appropriate permission, you would expect an error message. The command is executed successfully and the caller does not get an error regardless of the object's existence and user permissions! Here are the results when a user wants to drop an object using the conditional DROP statement:
- The object exists; user has permissions: When the object is removed, everything is fine.
- The object does not exist; user has permissions: There are no error messages displayed.
- The object exists; user does not have permissions: When the object is not removed, no error messages are displayed. The caller does not get the information that the object still exists; its DROP command has been executed successfully!
- The object does not exist; user does not have permissions: There are no error messages displayed.
You can read more about this inconsistency in the blog post DROP IF EXISTS aka D.I.E. at https://milossql.wordpress.com/2016/07/04/drop-if-exists-aka-d-i-e/.
This enhancement is handy; it helps you to abbreviate your code and it is intensively used by consultants, trainers, and conference speakers. They usually create database objects to demonstrate a feature, code technique, or behavior and then drop them from the system. And they do this again and again. However, conditional DROP statements will not be used so often in production systems. How often do you remove database objects from SQL Server? Very rarely, right? You sometimes drop them, when you perform a cleanup or remove some intermediate database objects. However, in most cases, you change existing or add new objects to a database.
More often, you use conditional DROP statements to create or alter objects rather than to remove them from a production system; therefore, the feature in the next section is more important and useful.