Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

DROP IF EXISTS: A Cool Feature in SQL Server 2016

DZone's Guide to

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.

· Database Zone
Free Resource

Navigating today's database scaling options can be a nightmare. Explore the compromises involved in both traditional and new architectures.

In 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.

TraditionalWay_Indiandotnet

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_INDIANDOTNET

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_INDIANDOTNET

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!

Planning for disaster doesn't have to actually be a disaster. Understand your options for deploying a database across multiple data centers - without the headache.

Topics:
data ,table ,database ,sql server 2016

Published at DZone with permission of Rajat Jaiswal, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}