In this blog post, we will discuss a new feature introduced in SQL Server 2016 called the “Drop if Exists” clause. This feature allows you to easily drop tables or stored procedures if they exist in the database.

Many people may not be aware of this feature, as it has been available in MySQL for quite some time. If you are a MySQL DBA, you may find it interesting that SQL Server has now introduced this feature as well.

Let’s take a look at an example to see how this feature works. First, we will create a sample table:

CREATE TABLE test(id INT, name VARCHAR(100));

If we want to drop the above table, we can execute the following script:

DROP TABLE IF EXISTS test;

The above script will drop the table named “test” if it exists. If the table does not exist, it will display a warning instead of an error. This is important to understand, as it allows us to easily handle the situation in our code.

Not only tables, but MySQL also has a similar feature available for stored procedures. Let’s see a quick example for the same. Here is a script to create a stored procedure:

CREATE PROCEDURE test_proc
AS
BEGIN
SELECT 1 AS number;
END;

After creating the above procedure, you can use the following script to drop it:

DROP PROCEDURE IF EXISTS test_proc;

Just like with tables, this script will drop the stored procedure if it exists. If the stored procedure does not exist, it will return a warning.

It is interesting to note that MySQL has had this feature for a while, while SQL Server has only recently introduced it in the 2016 version.

In conclusion, the “Drop if Exists” clause in SQL Server is a useful feature that allows you to easily drop tables or stored procedures if they exist in the database. This can be particularly helpful when you are not sure about the existence of the object in advance.