In my first post in the series “SQL Server 2016 Features for Developers” I will write about probably the smallest one – conditional DROP statement or DROP IF EXISTS.
With a conditional DROP statement, you can avoid getting an exception if the object you want to drop does not exist. If a table you want to drop is already removed or it was not created at all, the DROP TABLE statement will fail with the following error message:
Msg 3701, Level 11, State 5, Line 4
Cannot drop the table 'dbo.MyTable', because it does not exist or you do not have permission.
SQL Server 2016 introduces conditional DROP statement for most of the database objects. Conditional DROP statement is a DROP statement extended with the IF EXISTS part. Let us repeat the above command with this extended syntax:
DROP TABLE IF EXISTS dbo.MyTable;
You can execute this statement any number of times and you will get no error. To achieve this prior to SQL Server 2016 we had to check the existence of the object before we remove it, like in this code:
IF OBJECT_ID('dbo.MyTable ','U') IS NOT NULL
DROP TABLE dbo.MyTable;
So, one code line more and it is error prone – you have to write the name of the object twice. It’s not a big deal, but this new form is shorter and not error prone.
Conditional DROP statement is supported for most of the objects, but not for all of them. For instance, you cannot use it to conditionally remove a partitioned function. The full list of supported objects you can find in BOL.
How does IF EXISTS work? It simply suppresses the error message. This is exactly what we need if the reason for the error is non-existence of the object, but if user who wants to drop the object does not have appropriate permission, we would expect an error message. However, the command is executed successfully and the caller does not get an error regardless of the object existence and user permissions! To demonstrate this let’s create first a test login and database user in the new SQL Server sample database WideWorldImporters. In addition we will create a single column test table Z90.
USE WideWorldImporters;
--Create a test login and database user
CREATE LOGIN TestConditionalDrop WITH PASSWORD = 'A$§h8_098hkdg&!';
CREATE USER TestConditionalDrop FOR LOGIN TestConditionalDrop;
--Create test table
CREATE TABLE dbo.Z90(id INT);
Now we’ll try to drop newly created and a non-exiting table with this new account. This account does not have even SELECT permissions. Let’s check this:
--Switch to user TestConditionalDrop
EXECUTE AS USER = 'TestConditionalDrop';
--try to read the table Z90
SELECT * FROM dbo.Z90;
Let’s try now to drop a non-existing table dbo.Z91 and the existing dbo.Z90:
--try to drop a non-existing table
DROP TABLE dbo.Z91;
/*
Msg 3701, Level 11, State 5, Line 31
Cannot drop the table 'dbo.Z91', because it does not exist or you do not have permission.*/
--try DROP an existing table
DROP TABLE dbo.Z90;
/*
Msg 3701, Level 14, State 20, Line 37
Cannot drop the table 'Z90', because it does not exist or you do not have permission.*/
So, in both cases we got the same message, because the newly created account does not have permissions to drop tables. However, when we try the same with the conditional DROP statement:
--Ensure that statement is executed in the context of new user
SELECT SUSER_NAME();
--drop a non-existing table
DROP TABLE IF EXISTS dbo.Z91;
--drop an existing table
DROP TABLE IF EXISTS dbo.Z90;
--back to the original user
REVERT;
--check if the table dbo.Z9 exists
SELECT OBJECT_ID('dbo.Z90','U');
--1351675863
No error messages at all! DROP IF EXISTS simply suppressed the error message and the command caller can think that the action has been executed successfully. Of course, the existing table was not removed, due to insufficient permissions, but this information should be shared with the command caller and I see this behavior as a bug. It is OK to suppress the error message when the object does not exist, but if permissions were the problem, the exception should be raised.
When we would ignore a previous bug, this enhancement is handy; it helps you abbreviate your code, and it will be intensive used by… consultants, trainers and conference speakers. They usually create database objects to demonstrate some feature, code technique or behavior and then drop them from the system. And they do this again and again.
However, conditional DROP statement will not be used so often in the production systems. How often do we remove database objects from SQL Server? Very rare, right? When we perform some cleanup or remove intermediate database objects. In most of the cases we add new or change existing objects. Therefore, I would like to see similar implementation for the object’s creation or updating. To extend Transact-SQL syntax with, for instance, CREATE OR ALTER or CREATE or REPLACE command. This would more important for script deployment then DROP statement extensions. I hope, we’ll see it in the next SQL Server version.
Thanks for reading.
