New Drop If Exists Syntax in SQL Server 2016

By: John Miner   |   Updated: 2016-08-22   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > SQL Server 2016

Problem

When writing T-SQL code, we often write code to check if the database object exists first and then take some action.  Is there an easier way to do this in SQL Server 2016?

Solution

Microsoft SQL Server 2016 was released to manufacturing on June 1st. Please see this SQL Server Team BLOG for the full details.

This product release contains many new features in the database engine. One new feature is the DROP IF EXISTS syntax for use with Data Definition Language (DDL) statements. Twenty existing T-SQL statements have this new syntax added as an optional clause. Please see the "What's New in Database Engine" article for full details.  In this tip we will use examples of how this new feature can be used.

Business Problem for Example

A local business owner has asked you for help to keep track of his inventory. His business is centered on buying, assembling and customizing model cars for wealthy clients. Did you know that the toy industry has an estimated annual sales of 25 billion dollars with toy models taking a 1.5 billion dollar share?

Prior Knowledge

This article assumes you know how to create a Azure SQL Server and Azure SQL Database. You should be familiar with writing Transact SQL (T-SQL) in the SQL Server Management Studio (SSMS) since we will be leveraging this interactive development environment (IDE) to craft the business solution.

Please make sure you are using the latest version of SSMS. This product was separated from the database installation as a different product. This separation allows the development team to release updates on the IDE on a monthly schedule versus updates on the database on a yearly schedule. The July 2016 release contains many new features.

Azure SQL Server Objects

We will be reusing the Azure SQL Server that I created last time. The server name is mssqltips2016, the administrator login name is jminer, and the firewall rule name is MyLaptop. We will be creating a new database named AUTOS to contain the database objects for our business solution.

If you do not have these objects in your Azure Portal, please follow the steps my previous article to get to this point.

Article Syllabus

I will be using the model car business as a case study for showing coding examples for 15 out of the 20 data definition language statements impacted by the new syntax. The last 5 statements are not used in common practice by most developers. Each statement will be showcased with restart able T-SQL code that uses the algorithm below.

1. Test existence of database object.
2. Drop database object if it exists.
3. Create new database object.

The new DROP IF EXISTS syntax replaces the old block of code that used system catalog views to determine the existence of an object. Basically, the new syntax combines steps one and two into a smaller set of code to produce the same results.

For each statement, I give an example of both the old and new way to accomplish the same task. If the CREATE or DROP statements are executed on existing or missing objects respectively, errors will be generated. I show the output of such negative test cases. Last but not least, hyperlinks to each statement are given for the reader to look up detailed information if they desire.

SQL Server Drop Database If Exists

A database object is the main container in which other user defined objects are stored within. This definition becomes crystal clear when you use Azure SQL Database. Let's get this article started by creating a database named [AUTOS] for our business solution.

Make sure you are in a session connected to the [master] database when you execute the T-SQL code below.

/*
    Create autos database
*/

-- Old block of code
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS')
    DROP DATABASE [AUTOS]
GO
 
-- New block of code
DROP DATABASE IF EXISTS [AUTOS]
GO

-- Add new database
CREATE DATABASE [AUTOS] 
(
MAXSIZE = 2GB,
EDITION = 'STANDARD',
SERVICE_OBJECTIVE = 'S0'
)
GO 

One might ask what happens when you try to execute DROP DATABASE statement on non-existing database? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE DATABASE statement on existing database? The following error message is generated.

SQL Server Drop Schema If Exists

I personally think that the schema object is underutilized in database design. This object allows the designer to secure and/or hide groups of objects at a higher level. If you do not use custom schema objects, the default schema [dbo] does exist.   However, you are probably granting privileges at the lower level such as a table or view.

In our business solution, we want to create a [TOY] schema for active data and/or reference tables; and an [AUDIT] schema to trace events leading up to the current database state.

Going forward, make sure you execute any code in session connected to the [AUTOS] database.

The T-SQL code below creates the required schemas.

/*
    Create toy schema
*/
 
-- Old block of code
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'TOY')
DROP SCHEMA [TOY]
GO
 
-- New block of code
DROP SCHEMA IF EXISTS [TOY]
GO

-- Add new schema.
CREATE SCHEMA [TOY] AUTHORIZATION [dbo]
GO


/*
    Create audit schema
*/
 
-- Old block of code
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'AUDIT')
DROP SCHEMA [AUDIT]
GO
 
-- New block of code
DROP SCHEMA IF EXISTS [AUDIT]
GO

-- Add new schema.
CREATE SCHEMA [AUDIT] AUTHORIZATION [dbo]
GO

One might ask what happens when you try to execute DROP SCHEMA statement with non-existing schema? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE SCHEMA statement with an existing schema? The following error message is generated.

SQL Server Drop Table If Exists

A table is the key storage object in any relational database management system (RDBMS). We will start building our business solution with one active table, one audit table and two reference tables.

A reference table allows the designer to store an integer in the main on line transaction processing (OLTP) table instead of a lengthy description. This could same a ton of disk space if the table contains millions of rows.

The T-SQL code below creates the [TOY].[BRANDS] table to hold names of automobile manufacturers.

/*
    Create brands table 
*/
 
-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[TOY].[BRANDS]') AND type in (N'U'))
DROP TABLE [TOY].[BRANDS]
GO

-- New block of code
DROP TABLE IF EXISTS [TOY].[BRANDS]
GO

-- Add new table
CREATE TABLE TOY.BRANDS
(
ID INT NOT NULL,
NAME VARCHAR(20) NULL
)
GO
 
-- Load the table with data
INSERT INTO TOY.BRANDS (ID, NAME) VALUES
(1, 'Ford'),
(2, 'Chevy'),
(3, 'Dodge'),
(4, 'Plymouth'),
(5, 'Oldsmobile'),
(6, 'Lincoln'),
(7, 'Mercury');
GO

One might ask what happens when you try to execute DROP TABLE statement with non-existing table? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE TABLE statement with an existing table? The following error message is generated.

SQL Server Drop Constraint If Exists

Constraints are used in database design to force data integrity at the column level as well as referential integrity at the table level. CHECK and DEFAULT constraints come to mind when dealing with columns; and PRIMARY KEY and FORIEGN KEY constraints are used with tables. See table constraints for more details.

We want to have the ID column be a primary key in our [TOY].[BRANDS] table. The T-SQL code below creates the requested constraint named PK_TOY_BRANDS_ID.

/*
    Create primary key
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects WHERE name = N'PK_TOY_BRANDS_ID' AND type = N'PK')
ALTER TABLE [TOY].[BRANDS] DROP CONSTRAINT PK_TOY_BRANDS_ID;
GO

-- New block of code
ALTER TABLE [TOY].[BRANDS] DROP CONSTRAINT IF EXISTS PK_TOY_BRANDS_ID;
GO

-- Create constraint
ALTER TABLE [TOY].[BRANDS] WITH CHECK   
ADD CONSTRAINT PK_TOY_BRANDS_ID PRIMARY KEY CLUSTERED (ID)
GO

One might ask what happens when you try to execute ALTER TABLE - DROP CONSTRAINT statement on a table without an existing constraint? The following error message is generated.

Or, one might ask what happens when you try to execute ALTER TABLE - ADD CONSTRAINT statement on a table with an existing constraint? The following error message is generated.

SQL Server Drop Index If Exists

There are twelve different index types listed on MSDN for SQL Server 2016. The most commonly used indexes are CLUSTERED and NON-CLUSTERED. Some indexes like a PRIMARY KEY and UNIQUE index guarantee that only one occurrence of the information will reside in a given table.

We want to have the NAME column be a unique in our [TOY].[BRANDS] table. The T-SQL code below creates the requested index named IDX_TOY_BRANDS_NAME.

/*
    Create unique index 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.indexes WHERE NAME = N'IDX_TOY_BRANDS_NAME') 
DROP INDEX [IDX_TOY_BRANDS_NAME] ON [TOY].[BRANDS]
GO

-- New block of code
DROP INDEX IF EXISTS [IDX_TOY_BRANDS_NAME] ON [TOY].[BRANDS]
GO

-- Add new index
CREATE UNIQUE INDEX [IDX_TOY_BRANDS_NAME] ON [TOY].[BRANDS] (NAME);  
GO

One might ask what happens when you try to execute DROP INDEX statement on a table without an existing index? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE INDEX statement on a table with an existing index? The following error message is generated.

SQL Server Drop Sequence If Exists

The sequence object was added to the database engine in SQL Server 2012. I think this was mainly added to support customers who were moving from Oracle to SQL Server. Before this object was introduced, the IDENTITY property of a numeric column could be defined to create a sequence of numbers. This property is bound to the column that it is defined on.

In contrast, the sequence object is created at the schema level. Thus, two tables theoretically could use the same sequence object for auto incrementing numeric columns. I do not know a business case that would need this functionality. However, it is definitely possible to create this scenario.

The steps below outline how to create and attach a sequence to a column. The last three steps test the new default constraint.

1. Drop sequence object if it exists.
2. Create new sequence object.
3. Drop default constraint if it exists.
4. Create new default constraint.
5. Truncate table.
6. Restart sequence value.
7. Insert data into table.

It is important to note that the NEXT VALUE FOR function does all the heavy lifting in the default constraint. The T-SQL code below creates a sequence named [TOY].[SEQ_BRANDS_ID] that is used in the default constraint named DF_TOY_BRANDS_ID .

/*
    Create a sequence object
*/

-- Old block of code
IF EXISTS (SELECT name FROM sys.sequences WHERE name = N'SEQ_BRANDS_ID')
    DROP SEQUENCE [TOY].[SEQ_BRANDS_ID]
GO

-- New block of code
DROP SEQUENCE IF EXISTS [TOY].[SEQ_BRANDS_ID]
GO

-- Add new sequence.
CREATE SEQUENCE [TOY].[SEQ_BRANDS_ID]
    AS INT
    START WITH 100
    INCREMENT BY 1
    MINVALUE 1
    NO MAXVALUE 
    NO CYCLE
    NO CACHE;
GO


/*
    Add default constraint
*/

-- New block of code
ALTER TABLE [TOY].[BRANDS] DROP CONSTRAINT IF EXISTS DF_TOY_BRANDS_ID;
GO

-- Create constraint
ALTER TABLE [TOY].[BRANDS]    
ADD CONSTRAINT DF_TOY_BRANDS_ID DEFAULT (NEXT VALUE FOR [TOY].[SEQ_BRANDS_ID]) FOR ID;
GO


/*
    Reload data
*/

-- Alter the sequence.
ALTER SEQUENCE [TOY].[SEQ_BRANDS_ID] RESTART WITH 1;
GO

-- Remove data
TRUNCATE TABLE TOY.BRANDS;
GO

-- Load the table with data
INSERT INTO TOY.BRANDS (NAME) VALUES
('Ford'),
('Chevy'),
('Dodge'),
('Plymouth'),
('Oldsmobile'),
('Lincoln'),
('Mercury');
GO

One might ask what happens when you try to execute DROP SEQUENCE statement on a schema without an existing sequence? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE SEQUENCE statement on a schema with an existing sequence? The following error message is generated.

Building Upon Our Example

To solve the business problem, we need some additional tables.

The [TOY].[STATUS_CODE] reference table describes a production step as completed, requires rework or should be scrapped.

/*
    Create status code table 
*/

-- Remove table if it exists
DROP TABLE IF EXISTS [TOY].[STATUS_CODE]
GO

-- Create a STATUS CODE table
CREATE TABLE TOY.STATUS_CODE
(
ID INT IDENTITY(1, 1) CONSTRAINT PK_TOY_STATUS_CODE_ID PRIMARY KEY CLUSTERED,
NAME VARCHAR(20) NULL CONSTRAINT IDX_TOY_STATUS_CODE_NAME UNIQUE 
)
GO

-- Load the table with data
INSERT INTO TOY.STATUS_CODE (NAME) VALUES
('Completed'),
('Rework Needed'),
('Scrapped');
GO

The [TOY].[CURRENT_STEP] contains one row for every model car. This row describes the current step and production state for a given model car. Foreign keys are used to enforce referential integrity between this main table and the two reference tables.

-- 
-- Create current step table 
--

-- Remove table if it exists
DROP TABLE IF EXISTS [TOY].[CURRENT_STEP]
GO


-- Create builds table (latest status)
CREATE TABLE [TOY].[CURRENT_STEP]
(
    [MODEL_ID] [varchar] (64) NOT NULL,
    [BRAND_ID] [int] NOT NULL,
    [START_DATE] [datetime] NOT NULL,
    [FINISH_DATE] [datetime] NULL,
    [MESSAGE_TEXT] [varchar] (max) NULL,
    [STATUS_ID] [int] NULL,
    CONSTRAINT [PK_TOY_CURRENT_STEP_VS] PRIMARY KEY CLUSTERED 
    ( 
        [VEHICLE_ID] ASC,  
        [START_DATE] ASC
    )
)
GO


-- 
-- Foreign key constraint 1
--

-- New block of code
ALTER TABLE [TOY].[CURRENT_STEP] DROP CONSTRAINT IF EXISTS FK_TOY_BRANDS_ID;
GO

-- Create constraint
ALTER TABLE [TOY].[CURRENT_STEP]   
ADD CONSTRAINT FK_TOY_BRANDS_ID FOREIGN KEY ([BRAND_ID]) REFERENCES [TOY].[BRANDS] (ID);
GO


-- 
-- Foreign key constraint 2
--

-- New block of code
ALTER TABLE [TOY].[CURRENT_STEP] DROP CONSTRAINT IF EXISTS FK_TOY_STATUS_ID;
GO

-- Create constraint
ALTER TABLE [TOY].[CURRENT_STEP]   
ADD CONSTRAINT FK_TOY_STATUS_ID FOREIGN KEY ([STATUS_ID]) REFERENCES [TOY].[STATUS_CODE] (ID);
GO

The [AUDIT].[HISTORY_STEP] table contains a row for every insert or update that was performed on the main table. Basically, every step of the production processing for a given model car is recorded in this table. No foreign keys are necessary since the data is coming from the [TOY].[CURRENT_STEP] table.

/* 
    Create history step table 
*/

-- Remove table if it exists
DROP TABLE IF EXISTS [AUDIT].[HISTORY_STEP]
GO


-- Create MAKE HISTORY table (All Records)
CREATE TABLE [AUDIT].[HISTORY_STEP]
(
    [SURROGATE_KEY] INT IDENTITY (1, 1),
    [MODEL_ID] [varchar] (64) NOT NULL,
    [BRAND_ID] [int] NOT NULL,
    [START_DATE] [datetime] NOT NULL,
    [FINISH_DATE] [datetime] NULL,
    [MESSAGE_TEXT] [varchar] (max) NULL,
    [STATUS_ID] [int] NULL,
    [DURATION] [int] NULL,
    CONSTRAINT [PK_TOY_HISTORY_STEP_VS] PRIMARY KEY CLUSTERED 
    ( 
        [MODEL_ID] ASC,  
        [START_DATE] ASC
    )
)
GO

SQL Server Drop Column If Exists

The database schema is a living entity within an organization. Unless your company is very static in nature, the actual definition of the table will change over time. Use the ALTER TABLE statement to ADD, DROP or ALTER the column definitions.

After creating our [TOY].[CURRENT_STEP] table, the business line has requested a column be added that calculates the duration of the action/step. The T-SQL code below adds the [DURATION] column to the table.

/*
    Add a new column
*/

-- Old block of code
IF EXISTS(SELECT * FROM SYS.columns WHERE name='DURATION' AND  
          OBJECT_ID = OBJECT_ID('[TOY].[CURRENT_STEP]'))
ALTER TABLE [TOY].[CURRENT_STEP] DROP COLUMN [DURATION]
GO

-- New block of code
ALTER TABLE [TOY].[CURRENT_STEP] DROP COLUMN IF EXISTS [DURATION];
GO

--  Add a computed column
ALTER TABLE [TOY].[CURRENT_STEP] ADD [DURATION] 
    AS (DATEDIFF(SECOND,[START_DATE],[FINISH_DATE])) PERSISTED;

One might ask what happens when you try to execute ALTER TABLE - DROP COLUMN statement on a table without an existing column? The following error message is generated.

Or, one might ask what happens when you try to execute ALTER TABLE - ADD COLUMN statement on a table with an existing column? The following error message is generated.

You are not seeing a mistake with the above hyper link. The COLUMN key word did not make it into the ADD clause during the creation of the ANSI SQL specification. However, it is the implied recipient of the action.

SQL Server Drop Trigger If Exists

Microsoft SQL Server currently supports three types of triggers: DML, DDL and LOGON.

Because we are interested in capturing any changes on the main table, we want to use a DML trigger. DML triggers are a special type of stored procedure that automatically fires when a data manipulation language (DML) event takes place that affects the table or view defined with the trigger. Events that can cause the trigger to fire include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules, data integrity, and audit trails.

Again, we have another choice to make since DML triggers can be defined to fire INSTEAD OF or AFTER a given action. We want to define an AFTER trigger since we do not want to prevent changes to the [TOY].CURRENT_STEP] table but we want to capture all actions in the [AUDIT].[HISTORY_STEP] table.

The T-SQL code below creates the required trigger named [TOY].[TRG_MOVE_CURRENT_2_HISTORY].

/*  
 Trigger to audit changes.
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = 
            OBJECT_ID(N'[TOY].[TRG_MOVE_CURRENT_2_HISTORY]'))
DROP TRIGGER [TOY].[TRG_MOVE_CURRENT_2_HISTORY]
GO

-- New block of code
DROP TRIGGER IF EXISTS [TOY].[TRG_MOVE_CURRENT_2_HISTORY]
GO

-- Move modified record to history table
CREATE TRIGGER [TOY].[TRG_MOVE_CURRENT_2_HISTORY] ON [TOY].[CURRENT_STEP]
FOR INSERT, UPDATE
AS
    INSERT INTO [AUDIT].[HISTORY_STEP]
    (
        [MODEL_ID],
        [BRAND_ID],
        [START_DATE],
        [FINISH_DATE],
        [MESSAGE_TEXT],
        [STATUS_ID],
        [DURATION]
    )
    SELECT
        [MODEL_ID],
        [BRAND_ID],
        [START_DATE],
        [FINISH_DATE],
        [MESSAGE_TEXT],
        [STATUS_ID],
        DATEDIFF(SECOND,[START_DATE],[FINISH_DATE])
    FROM inserted
GO

One might ask what happens when you try to execute DROP TRIGGER statement on a table without an existing trigger? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE TRIGGER statement on a table with an existing trigger? The following error message is generated.

SQL Server Drop Procedure If Exists

A stored procedure in SQL Server is a group of one or more compiled T-SQL statements. Procedures can accept input parameters, return multiple output parameters, contain programming statements that perform database operations and/or return a status value to a calling program to indicate success or failure.

To solve our business problem, we need to define a stored procedure that will insert data into the current step table if a record for a given toy car does not exists or update the existing record. This algorithm is commonly called an UPSERT procedure.

The T-SQL code below creates a stored procedure named [TOY].[UPSERT_STEP_DATA].

/*  
 Create upsert procedure 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[TOY].[UPSERT_STEP_DATA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [TOY].[UPSERT_STEP_DATA]
GO

-- New block of code
DROP PROCEDURE IF EXISTS [TOY].[UPSERT_STEP_DATA]
GO


-- Create the procedure
CREATE PROCEDURE [TOY].[UPSERT_STEP_DATA] (
    @MODEL_ID [varchar] (64),
    @BRAND_ID [int],
    @START_DATE [datetime],
    @FINISH_DATE [datetime],
    @MESSAGE_TEXT [varchar] (max),
    @STATUS_ID [int])
AS
BEGIN

    -- Worry about concurrency
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

    BEGIN TRAN
    
        -- Try the update first
        UPDATE [TOY].[CURRENT_STEP]
        SET 
            [BRAND_ID] = @BRAND_ID,
            [START_DATE] = @START_DATE,
            [FINISH_DATE] = @FINISH_DATE,
            [MESSAGE_TEXT] = @MESSAGE_TEXT,
            [STATUS_ID] = @STATUS_ID
        WHERE [MODEL_ID] = @MODEL_ID
          
        -- Did not update vehicle
        IF @@ROWCOUNT = 0        
        BEGIN
        
            -- Add new record
            INSERT INTO [TOY].[CURRENT_STEP] 
            SELECT
                @MODEL_ID,
                @BRAND_ID,
                @START_DATE,
                @FINISH_DATE,
                @MESSAGE_TEXT,
                @STATUS_ID
                
            -- Detected error
            IF @@ERROR <> 0
                ROLLBACK
               
        END
    
    -- Save the transaction
    COMMIT
    
END
GO

One might ask what happens when you try to execute DROP PROCEDURE statement without an existing procedure? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE PROCEDURE statement with an existing procedure? The following error message is generated.

Adding Test Data to Our SQL Server Example

Before we talk about user defined views and functions, we need to add test data to our database. I am not expert at building model cars. With that said, wikihow had an article on the required steps to assemble any type of toy model. We will use these steps as guidelines for creating our test data.

The T-SQL script below executes the following actions.

1. Truncate the two tables.
2. Call the upsert procedure 14 times.
3. Show data from the two tables.

/*  
 Clear tables
*/

-- Clear the tables
TRUNCATE TABLE [TOY].[CURRENT_STEP]
GO

TRUNCATE TABLE [AUDIT].[HISTORY_STEP]
GO


/*  
 Fictitious build of toy car
*/

-- Step 1
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 00:00', 
'2011-04-01 00:09', 'Read instructions.', 1;
GO

-- Step 2
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 00:10', 
'2011-04-01 00:29', 'Wash all parts.', 1;
GO

-- Step 3
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 00:30', 
'2011-04-01 00:39', 'Remove large pieces off spruce tree.', 1;
GO

-- Step 4
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 00:40', 
'2011-04-01 00:59', 'Paint small pieces on spruce tree.', 1;
GO

-- Step 5
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 01:30', 
'2011-04-01 01:39', 'Scrap paint off glue edges.', 1;
GO

-- Step 6
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 01:40', 
'2011-04-01 01:49', 'Test fit parts for issues', 1;
GO

-- Step 7
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 01:50', 
'2011-04-01 01:54', 'Use right amount of glue.', 1;
GO

-- Step 8
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 01:55', 
'2011-04-01 02:09', 'Glue parts after test fit.', 1;
GO

-- Step 9
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 02:20', 
'2011-04-01 02:29', 'Fill seams with body putty.', 1;
GO

-- Step 10
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 02:30', 
'2011-04-01 02:39', 'Use newspaper while painting.', 1;
GO

-- Step 11
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 02:40',
'2011-04-01 02:49', 'Use white craft glue for clear pieces.', 1;
GO

-- Step 12
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 02:50', 
'2011-04-01 02:59', 'Inspect painting/gluing for touch ups.', 2;
GO

-- Step 13
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 03:00', 
'2011-04-01 03:09', 'Inspect painting/gluing for touch ups.', 1;
GO

-- Step 14
EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 03:10', 
'2011-04-01 03:14', 'Sell the toy car.', 1;
GO


/*  
 Show the data
*/

SELECT * FROM [TOY].[CURRENT_STEP]
GO

SELECT * FROM [AUDIT].[HISTORY_STEP]
GO

The output below shows the test data that should be stored in the [TOY].[CURRENT_STEP] and [AUDIT].[HISTORY_STEP] tables.

SQL Server Drop View If Exists

A view is defined as a virtual table whose contents are defined by a query. The following list contains various ways to put a view to good use.

1. Abstract underlying tables so that users can only see certain columns.
2. Security can be placed on view to restrict users.
3. Indexing a view makes it materialized (physical) for speed.
4. Provide backward compatibility for tables that have changed.
5. Pre-compiled code that skips the query parsing phase.

Views can be used to provide business information to users that might not be savvy in joining, filtering, grouping and aggregating data using T-SQL. If a SQL developer works with the user to package this logic in a view, a simple SELECT statement can be used to pull the data into MS Excel for analysis.

In our hypothetical business case, the business user wants a list of cars that are ready to sell on EBAY.

The T-SQL code below creates a view named [TOY].[CARS_READY_TO_SELL].

/*  
 Create view 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[TOY].[CARS_READY_TO_SELL]') AND type = N'V')
DROP VIEW [TOY].[CARS_READY_TO_SELL]
GO

-- New block of code
DROP VIEW IF EXISTS [TOY].[CARS_READY_TO_SELL]
GO

-- Cars ready to sell
CREATE VIEW [TOY].[CARS_READY_TO_SELL]
AS
    SELECT  * 
    FROM [TOY].[CURRENT_STEP]
    WHERE MESSAGE_TEXT = 'Sell the toy car.';
GO

-- Use the view
SELECT * FROM  [TOY].[CARS_READY_TO_SELL]
GO

One might ask what happens when you try to execute DROP VIEW statement without an existing view? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE VIEW statement with an existing View? The following error message is generated.

The output below is from selecting all data from our view.

SQL Server 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). Wayne Sheffield who is a SQL Server MCM and MVP has shown that inline table value functions (ITVF) are the quickest way to solve a problem. Please take a look at his article for full details.

We have been asked by the business line to create a function that returns the start time, end time and total duration for building a particular model. Our solution should be a ITVF so that we can call it for a single model or use it in a CROSS APPLY statement for all models.

The T-SQL code below creates the requested function named [TOY].[TOTAL_BUILD_TIME].

/*  
 Create function 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[TOY].[CARS_READY_TO_SELL]') AND type = N'V')
DROP FUNCTION [TOY].[TOTAL_BUILD_TIME]
GO

-- New block of code
DROP FUNCTION IF EXISTS [TOY].[TOTAL_BUILD_TIME]
GO

-- Total Build Time - Inline TVF
CREATE FUNCTION [TOY].[TOTAL_BUILD_TIME] 
    ( @MODEL_ID [varchar] (64) )
RETURNS TABLE
AS
RETURN 
(
 SELECT 
  [MODEL_ID], 
  MIN(START_DATE) AS BUILD_START, 
  MAX(FINISH_DATE) AS BUILD_END, 
  DATEDIFF(N, MIN(START_DATE), MAX(FINISH_DATE)) AS TOTAL_MINS 
 FROM 
  [AUDIT].[HISTORY_STEP] 
 WHERE 
  [MODEL_ID] = @MODEL_ID
 GROUP BY 
  [MODEL_ID]
);
GO

-- Sample call to TVF
SELECT * FROM [TOY].[TOTAL_BUILD_TIME] ('MUSTANG-1966-0001');
GO

One might ask what happens when you try to execute DROP FUNCTION statement without an existing function? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE FUNCTION statement with an existing function? The following error message is generated.

The output below is from selecting data from our inline table value function for the first 1966 Ford Mustang model.

SQL Server Drop Synonym If Exists

The main purpose of the construct is to provide a simple name to reference a database object that is addressed by a two, three or four part notation. If you do not supply a schema name when defining a synonym, the database engine assumes you want to use the default schema named [dbo]. This same assumption is used when crafting a SELECT statement without supplying a fully qualified table name.

The business users in the model toy company only have access to the [TOY] schema. You have been asked to present the [AUDIT].[HISTORY_STEP] for reviewing builds that have issues. One way to complete this request without duplicating data is to create a SYNONYM.

The T-SQL code below created the requested SYNONYM named [TOY].[ALL_BUILD_STEPS].

/*  
 Create synonym
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.synonyms WHERE NAME = 'ALL_BUILD_STEPS' and 
    SCHEMA_ID = (SELECT schema_id FROM SYS.SCHEMAS WHERE NAME = 'TOY'))
DROP SYNONYM [TOY].[ALL_BUILD_STEPS]
GO

-- New block of code
DROP SYNONYM IF EXISTS [TOY].[ALL_BUILD_STEPS]
GO

-- Create a simple synonym
CREATE SYNONYM [TOY].[ALL_BUILD_STEPS]  
FOR [AUDIT].[HISTORY_STEP];  
GO  

-- Show the data
SELECT * FROM [TOY].[ALL_BUILD_STEPS];
GO

One might ask what happens when you try to execute DROP SYNONYM statement without an existing synonym? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE SYNONYM statement with an existing synonym? The following error message is generated.

SQL Server Drop Type If Exists

The TYPE object is used to give a name to commonly used column definitions. In a nutshell, it is short hand for the actual column definition. For instance, the sysnames data type is defined as a NVARCHAR (128) and is used in many of the system catalog views where object names are defined.

The T-SQL code below creates a type named [TOY].[MODEL_ID_ADT] to be used where model id is defined.

/*  
 Create type
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.types WHERE NAME = 'MODEL_ID_ADT' and 
    SCHEMA_ID = (SELECT schema_id FROM SYS.SCHEMAS WHERE NAME = 'TOY'))
DROP TYPE  [TOY].[MODEL_ID_ADT]
GO

-- New block of code
DROP TYPE IF EXISTS [TOY].[MODEL_ID_ADT]
GO

-- Create the abstract data type (ADT)
CREATE TYPE [TOY].[MODEL_ID_ADT]  
FROM VARCHAR (64) NOT NULL;  

One might ask what happens when you try to execute DROP TYPE statement without an existing type? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE TYPE statement with an existing type? The following error message is generated.

SQL Server Drop User If Exists

What purpose does a database have if there are no users to search and retrieve data? Azure SQL database allows you to define contained database users. There is no reason for logins to be defined in the master database unless the account is used for administration. Microsoft is pushing for this design to guarantee higher SLA's with it's Platform As A Service offerings (PAAS). You may be required in the future to switch to this design.

At this time, I want to thank Jeremy Kadlec for allowing me to share my knowledge on MSSQLTips.com. The T-SQL code below adds [jkadlec] as a contained database user.

/*  
 Create user
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'jkadlec' and type = 'S')
DROP USER [jkadlec]
GO

-- New block of code
DROP USER IF EXISTS [jkadlec]
GO

-- Create contained db user
CREATE USER [jkadlec]  
WITH PASSWORD='MS#sql*Tips', DEFAULT_SCHEMA=[dbo]
GO    

One might ask what happens when you try to execute DROP USER statement without an existing user? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE USER statement with an existing user? The following error message is generated.

SQL Server Drop Role If Exists

Microsoft introduced the concept of user defined database roles in SQL Server 2008. Roles allow you to group users into one database level securable. The default database user is [dbo] and we can assign authorization of the role to this user. Use GRANT, REVOKE and/or DENY to give the role privileges to the database.

If you use database roles and schemas together for a security deployment plan, you will be managing security at a higher level with less management overhead.

The T-SQL code below creates a user defined database role named [mssqltips] and adds / drops user [jkadlec] as a member.

/*  
 Create role
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'mssqltips' and type = 'R')
DROP ROLE [mssqltips]
GO

-- New block of code
DROP ROLE IF EXISTS [mssqltips]
GO

-- Create user defined role 
CREATE ROLE [mssqltips] AUTHORIZATION dbo;  
GO 


-- Add user to role 
ALTER ROLE [mssqltips] ADD MEMBER [jkadlec];  
GO

-- Del user from role
ALTER ROLE [mssqltips] DROP MEMBER [jkadlec];
GO  

One might ask what happens when you try to execute DROP ROLE statement without an existing role? The following error message is generated.

Or, one might ask what happens when you try to execute CREATE ROLE statement with an existing role? The following error message is generated.

More SQL Server Drop If Exists Statements

At the beginning of this article, I stated that 5 of the 20 statements impacted by this new syntax are not used in common practice by most developers.

Listed below are the statements that I did not talk about.

1. DROP DEFAULT - This statement is deprecated and will be removed in the future. Use a DEFAULT constraint instead.
2. DROP RULE - This statement is deprecated and will be removed in the future. Use a CHECK constraint instead.
3. DROP ASSEMBLY - Common language runtime assembly is not support in Azure. Use for quicker algorithm execution with on-premises only version.
4. DROP AGGREGATE - Common language runtime aggregates are not support in Azure. Use for quicker algorithm execution with on-premises only version.
5. DROP SECURITY POLICY - This statement is used with row level security. It is a brand new feature released in SQL Server 2016.

Summary

The new DROP IF EXISTS optional clause has been added to twenty data definition language statements. This syntax reduces the amount of code that used to be required to create a T-SQL script that is truly restart able. The model car business was used as a fictitious case study to show examples for 15 of the 20 statements.

I suggest you start using this new syntax for scripts that will be deployed on database engines with level 13.0 and above compatibility. If your scripts still have to be deployed on older database engines, keep on using the older syntax.

Next Steps

• Read more tips on SQL Server 2016

About the author

John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips