by

“Drop If Exists” Syntax in Sql-Server 2016

With the recent Sql server 2016 Service Pack 1, one important feature “Drop If Exists” syntax has been added, which developers were missing from long time.Prior to Sqlserver 2016 developers need to check database objects existence before creating.

Prior to sql server 2016,If Exist clause was used to check the db object existence then developers were taking appropriate action if objects exists.

If the Database objects doesn’t exists it will not raise any error, it will continue executing the next statement in the batch.

If Exist In Older Versions:

Previously you need to add an IF EXISTS() condition to check if the database object already exists or not. If exists then drop and then create a new objects, like:

IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'DatabaseName')
  Do your thing...

Example:
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'Sql2016DB')
drop database Sql2016DB

New Way :

You can see new syntax much easier and shorter as compare to older syntax.Developer’s not need to memories whole long syntax to check and drop database objects.

Query Syntax: DROP <DbObject> [ IF EXISTS ] <ObjectName>

Here DbOject can be any database,store procedure,functions,Tables or triggers.

Let See  real-time examples for few important database objects:

1)Drop Database if Exists:

Now query syntax to drop the database is very easy and shorter.

Syntax:  Drop Database If Exists  <DatbaseName>

Example: let say our database  name is SqlDb2016 than below query will drop database by using new query syntax.

Drop database if exists SqlDb2016

d1

2. Drop Tables If Exists:

Syntax: Drop Database If Exists <TableName>

Example: let “EmployeeMaster” is a table in Sqldb2016 database than below query will drop table if exists in database.

Old Syntax :

d3.PNG

New query Syntax in Sql Server 2016

d2.PNG

3.Drop Procedure If Exist:

Drop procedure query is very often to use during the complex procedure writing and developers needs to apply some logic to check whether procedure is exists in database or not.

New “Drop If Exists” query syntax make developers life easy and new syntax is more short and memorable.

Old Query Syntax:

d4.PNG

 

New Query Syntax:

d5.PNG

4.) Drop Function If Exists:

User-defined functions are routines that accept parameters, perform an action and return the result of that action as a value. The return value can either be a single scalar value or a result set (table).

Old Query Syntax:

d6.PNG

New Query Syntax:

d7.PNG

5.) Drop Views If Exists:

A view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Old Query Syntax:

d8

New Query Syntax:

d9.PNG

6.)Drop Trigger If Exists:

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

Old Query Syntax:

d10.PNG

New Query Syntax:

d11.PNG

Drop if Exists statement is not limited to above mentioned database objects it can be use with other database objects like :

  • Index
  • constraints
  • Columns
  • Schema
  • Synonym
  • Type
  • Users
  • Role

 

Leave a comment