jump to navigation

Drop if Exists T-SQL Enhancement statement in SQL Server 2016 December 23, 2016

Posted by fofo in Sql Server, SQL Server 2016.
Tags:
trackback

This is the fourth post in a series of posts about the new features of SQL Server 2016. You can find the first post in the SQL Server 2016 (JSON support in SQL Server 2016) series here.

A post on Row Level Security in SQL Server 2016 can be found here.

A post on Temporal Tables in SQL Server 2016 can be found here .

SQL Server 2016 was officially released back in June 2016.

I have installed SQL Server 2016 Enterprise edition in my machine but you can use the SQL Server 2016 Developer edition or Express edition as well.

You can download the SQL Server 2016 Developer edition by joining the Visual Studio Dev Essentials which is free and simple. After joining you will be able to download SQL Server 2016 developer edition in your machine.

If we need to delete a database object e.g a table or a trigger, but we don’t know if they exist or not, then we are used to writing code like this e.g “If the table exists” then drop it  or “If the trigger exists” then drop it.

There is a new syntax “If EXISTS” in SQL Server 2016 that makes our life easier when it comes to drop objects.

You can use it to drop any database object you want.

Let’s move on and demonstrate the new enhancement with a hands-on demo.

Type (copy paste) the following query in a new query window and execute it.


CREATE DATABASE sampleDB;

GO


USE sampleDB;

GO

CREATE TABLE Product
 (
 Pid INT PRIMARY KEY
 IDENTITY ,
 pname NVARCHAR(50) ,
 price DECIMAL(18, 4)
 );


SELECT *
FROM sys.tables;

IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL
 DROP TABLE dbo.Product;

SELECT *
FROM sys.tables;


CREATE TABLE Product
 (
 Pid INT PRIMARY KEY
 IDENTITY ,
 pname NVARCHAR(50) ,
 price DECIMAL(18, 4)
 );

SELECT *
FROM sys.tables;


DROP TABLE IF EXISTS dbo.Product;

SELECT * FROM sys.tables;

Let me explain what I am doing in the code above.

I create a sample database and then create a table in it. Then I query the sys.tables and my table appears in the results since it exists.

Then I use the old syntax to check if the table exists and drop it.

Then I query again the sys.tables and there are no rows returned since the table is dropped.

Then I create the sample table again. Again I query the sys.tables and my table appears in the results since it has been recreated.

Then I use the new syntax to drop it “DROP TABLE IF EXISTS dbo.Product;”

Then I query again the sys.tables and there are no rows returned since the table is dropped.

This is handy new T-SQL enhancement in SQL Server 2016.

Hope it helps!!!

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: