Sql Developer Connection Is Currently Busy Sql Developer Try Again?

By:   |   Updated: 2016-08-22   |   Comments (8)   |   Related: More than > SQL Server 2016

Problem

When writing T-SQL code, we often write code to check if the database object exists first and so take some activity.  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 run into this SQL Server Team BLOG for the full details.

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

Business Problem for Example

A local concern owner has asked you for assistance to keep rail of his inventory. His business is centered on ownership, assembling and customizing model cars for wealthy clients. Did you know that the toy manufacture has an estimated annual sales of 25 billion dollars with toy models taking a ane.5 billion dollar share?

Prior Knowledge

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

Delight brand sure y'all are using the latest version of SSMS. This product was separated from the database installation as a different product. This separation allows the evolution squad 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

Nosotros will be reusing the Azure SQL Server that I created last fourth dimension. The server proper 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 comprise the database objects for our business organization solution.

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

Article Syllabus

I volition exist using the model motorcar business as a case written report for showing coding examples for xv out of the twenty data definition linguistic communication statements impacted by the new syntax. The terminal 5 statements are not used in common exercise past about developers. Each argument will be showcased with restart able T-SQL lawmaking that uses the algorithm below.

1. Exam existence of database object.
2. Drib database object if it exists.
3. Create new database object.

The new DROP IF EXISTS syntax replaces the old cake of code that used organization itemize views to determine the existence of an object. Basically, the new syntax combines steps one and ii into a smaller set up of code to produce the aforementioned results.

For each statement, I give an example of both the former and new way to attain the aforementioned job. 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 to the lowest degree, 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 y'all utilize Azure SQL Database. Let'south become this article started by creating a database named [AUTOS] for our business organisation solution.

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

/*     Create autos database */  -- Old block of code IF EXISTS (SELECT name FROM sys.databases WHERE proper noun = North'AUTOS')     Driblet 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' ) Get          

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

Or, 1 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 call up that the schema object is underutilized in database pattern. This object allows the designer to secure and/or hide groups of objects at a higher level. If yous do not use custom schema objects, the default schema [dbo] does be.   However, yous are probably granting privileges at the lower level such as a table or view.

In our business solution, we desire to create a [TOY] schema for active information and/or reference tables; and an [Audit] schema to trace events leading up to the current database land.

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 */   -- Erstwhile block of lawmaking IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'TOY') Drib SCHEMA [TOY] Become   -- 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 lawmaking IF EXISTS (SELECT * FROM sys.schemas WHERE proper noun = Due north'Inspect') DROP SCHEMA [Inspect] Become   -- New block of code DROP SCHEMA IF EXISTS [AUDIT] GO  -- Add new schema. CREATE SCHEMA [Inspect] AUTHORIZATION [dbo] GO          

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

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

SQL Server Drop Table If Exists

A table is the key storage object in whatever relational database management organisation (RDBMS). We volition kickoff edifice our business solution with one active table, one inspect table and two reference tables.

A reference table allows the designer to shop an integer in the master 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 car manufacturers.

/*     Create brands tabular array  */   -- Quondam 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] Become  -- New block of lawmaking Drop TABLE IF EXISTS [TOY].[BRANDS] Get  -- Add together 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, Proper noun) VALUES (1, 'Ford'), (two, 'Chevy'), (three, 'Dodge'), (four, 'Plymouth'), (5, 'Oldsmobile'), (6, 'Lincoln'), (vii, 'Mercury'); Go          

One might inquire what happens when yous try to execute Driblet TABLE argument with non-existing table? The following error message is generated.

Or, i might enquire what happens when yous try to execute CREATE Tabular array statement with an existing tabular array? The post-obit error bulletin is generated.

SQL Server Driblet Constraint If Exists

Constraints are used in database pattern 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 Principal Fundamental and FORIEGN Cardinal constraints are used with tables. See tabular array constraints for more than details.

Nosotros want to accept the ID cavalcade exist a primary key in our [TOY].[BRANDS] table. The T-SQL code below creates the requested constraint named PK_TOY_BRANDS_ID.

/*     Create primary primal */  -- Onetime block of code IF  EXISTS (SELECT * FROM sys.objects WHERE name = N'PK_TOY_BRANDS_ID' AND type = N'PK') ALTER TABLE [TOY].[BRANDS] Driblet CONSTRAINT PK_TOY_BRANDS_ID; GO  -- New block of lawmaking ALTER Table [TOY].[BRANDS] DROP CONSTRAINT IF EXISTS PK_TOY_BRANDS_ID; Get  -- Create constraint ALTER TABLE [TOY].[BRANDS] WITH Cheque    Add CONSTRAINT PK_TOY_BRANDS_ID Chief KEY Clustered (ID) GO          

1 might ask what happens when you try to execute Alter Tabular array - Drop CONSTRAINT statement on a table without an existing constraint? The following error message is generated.

Or, one might inquire what happens when yous try to execute Alter TABLE - Add together CONSTRAINT statement on a table with an existing constraint? The following fault message is generated.

SQL Server Driblet Index If Exists

There are twelve dissimilar alphabetize types listed on MSDN for SQL Server 2016. The most commonly used indexes are CLUSTERED and Non-CLUSTERED. Some indexes like a Primary Central and UNIQUE index guarantee that only 1 occurrence of the data volition reside in a given table.

We want to accept the Name column exist 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 cake of code IF  EXISTS (SELECT * FROM sys.indexes WHERE NAME = Northward'IDX_TOY_BRANDS_NAME')  Driblet INDEX [IDX_TOY_BRANDS_NAME] ON [TOY].[BRANDS] GO  -- New cake of code DROP INDEX IF EXISTS [IDX_TOY_BRANDS_NAME] ON [TOY].[BRANDS] GO  -- Add new index CREATE UNIQUE Alphabetize [IDX_TOY_BRANDS_NAME] ON [TOY].[BRANDS] (Name);   Get          

I might ask what happens when yous try to execute DROP INDEX statement on a table without an existing alphabetize? The post-obit error message is generated.

Or, ane might ask what happens when y'all try to execute CREATE Alphabetize statement on a tabular array with an existing index? The following mistake 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 exist 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, 2 tables theoretically could use the same sequence object for auto incrementing numeric columns. I practise non know a business concern case that would need this functionality. However, information technology is definitely possible to create this scenario.

The steps below outline how to create and adhere a sequence to a column. The last three steps examination 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 part does all the heavy lifting in the default constraint. The T-SQL lawmaking 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 */  -- One-time cake of code IF EXISTS (SELECT name FROM sys.sequences WHERE proper noun = North'SEQ_BRANDS_ID')     DROP SEQUENCE [TOY].[SEQ_BRANDS_ID] Go  -- New block of code Drib SEQUENCE IF EXISTS [TOY].[SEQ_BRANDS_ID] GO  -- Add new sequence. CREATE SEQUENCE [TOY].[SEQ_BRANDS_ID]     As INT     Kickoff WITH 100     INCREMENT Past 1     MINVALUE 1     NO MAXVALUE      NO Bicycle     NO CACHE; GO   /*     Add default constraint */  -- New block of lawmaking ALTER TABLE [TOY].[BRANDS] DROP CONSTRAINT IF EXISTS DF_TOY_BRANDS_ID; Become  -- Create constraint ALTER TABLE [TOY].[BRANDS]     Add together 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 ane; GO  -- Remove data TRUNCATE TABLE TOY.BRANDS; Get  -- Load the table with data INSERT INTO TOY.BRANDS (Proper name) VALUES ('Ford'), ('Chevy'), ('Contrivance'), ('Plymouth'), ('Oldsmobile'), ('Lincoln'), ('Mercury'); GO          

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

Or, i might inquire what happens when you lot try to execute CREATE SEQUENCE statement on a schema with an existing sequence? The post-obit fault bulletin is generated.

Edifice Upon Our Instance

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

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

/*     Create status code table  */  -- Remove table if it exists Drib Tabular array IF EXISTS [TOY].[STATUS_CODE] Go  -- Create a STATUS Code tabular array CREATE Tabular array TOY.STATUS_CODE ( ID INT IDENTITY(1, 1) CONSTRAINT PK_TOY_STATUS_CODE_ID Chief Central Amassed, Proper name VARCHAR(20) Aught CONSTRAINT IDX_TOY_STATUS_CODE_NAME UNIQUE  ) GO  -- Load the tabular array with information INSERT INTO TOY.STATUS_CODE (NAME) VALUES ('Completed'), ('Rework Needed'), ('Scrapped'); GO          

The [TOY].[CURRENT_STEP] contains i row for every model car. This row describes the current step and production country for a given model motorcar. Strange keys are used to enforce referential integrity between this principal table and the two reference tables.

--  -- Create current stride table  --  -- Remove table if it exists DROP TABLE IF EXISTS [TOY].[CURRENT_STEP] Become   -- Create builds table (latest status) CREATE Tabular array [TOY].[CURRENT_STEP] (     [MODEL_ID] [varchar] (64) NOT NULL,     [BRAND_ID] [int] Not Zilch,     [START_DATE] [datetime] Non Nothing,     [FINISH_DATE] [datetime] Zilch,     [MESSAGE_TEXT] [varchar] (max) NULL,     [STATUS_ID] [int] NULL,     CONSTRAINT [PK_TOY_CURRENT_STEP_VS] Principal Key CLUSTERED      (          [VEHICLE_ID] ASC,           [START_DATE] ASC     ) ) Become   --  -- Foreign fundamental 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 lawmaking Alter TABLE [TOY].[CURRENT_STEP] Drop CONSTRAINT IF EXISTS FK_TOY_STATUS_ID; Go  -- Create constraint ALTER Table [TOY].[CURRENT_STEP]    Add together CONSTRAINT FK_TOY_STATUS_ID FOREIGN Primal ([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 tabular array. Basically, every step of the product 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] tabular array.

/*      Create history pace table  */  -- Remove tabular array 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, i),     [MODEL_ID] [varchar] (64) Not NULL,     [BRAND_ID] [int] NOT Nix,     [START_DATE] [datetime] Non NULL,     [FINISH_DATE] [datetime] Null,     [MESSAGE_TEXT] [varchar] (max) Cypher,     [STATUS_ID] [int] NULL,     [DURATION] [int] NULL,     CONSTRAINT [PK_TOY_HISTORY_STEP_VS] PRIMARY KEY Amassed      (          [MODEL_ID] ASC,           [START_DATE] ASC     ) ) Become          

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 tabular array will alter over time. Use the ALTER TABLE statement to Add, Drib or ALTER the cavalcade definitions.

Afterwards creating our [TOY].[CURRENT_STEP] table, the business organization line has requested a cavalcade be added that calculates the elapsing of the activity/step. The T-SQL code beneath adds the [DURATION] column to the tabular array.

/*     Add a new column */  -- Old block of lawmaking IF EXISTS(SELECT * FROM SYS.columns WHERE name='Elapsing' AND             OBJECT_ID = OBJECT_ID('[TOY].[CURRENT_STEP]')) Change TABLE [TOY].[CURRENT_STEP] Drib COLUMN [Elapsing] Get  -- New cake of code Modify TABLE [TOY].[CURRENT_STEP] DROP Cavalcade IF EXISTS [DURATION]; Get  --  Add a computed column ALTER Tabular array [TOY].[CURRENT_STEP] Add together [DURATION]      AS (DATEDIFF(SECOND,[START_DATE],[FINISH_DATE])) PERSISTED;          

One might ask what happens when y'all try to execute Modify Tabular array - DROP COLUMN argument on a tabular array without an existing column? The following error message is generated.

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

Yous are non seeing a error with the above hyper link. The COLUMN key word did non make information technology into the ADD clause during the creation of the ANSI SQL specification. Withal, it is the implied recipient of the action.

SQL Server Drop Trigger If Exists

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

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

Again, nosotros have another choice to brand since DML triggers can exist defined to fire INSTEAD OF or AFTER a given activeness. Nosotros want to define an Subsequently trigger since we do not want to forestall changes to the [TOY].CURRENT_STEP] table but we want to capture all deportment in the [Inspect].[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 lawmaking IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id =              OBJECT_ID(Northward'[TOY].[TRG_MOVE_CURRENT_2_HISTORY]')) Drib TRIGGER [TOY].[TRG_MOVE_CURRENT_2_HISTORY] GO  -- New block of code Drib TRIGGER IF EXISTS [TOY].[TRG_MOVE_CURRENT_2_HISTORY] Go  -- Motion 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 Become          

I might ask what happens when you lot try to execute Driblet TRIGGER statement on a table without an existing trigger? The following fault message is generated.

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

SQL Server Driblet Procedure If Exists

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

To solve our business problem, we need to define a stored procedure that volition insert data into the current footstep table if a record for a given toy motorcar 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 cake of code IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =              OBJECT_ID(N'[TOY].[UPSERT_STEP_DATA]') AND type in (N'P', North'PC')) DROP PROCEDURE [TOY].[UPSERT_STEP_DATA] GO  -- New block of code Driblet Procedure IF EXISTS [TOY].[UPSERT_STEP_DATA] GO   -- Create the process 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 mistake             IF @@Fault <> 0                 ROLLBACK                         Cease          -- Relieve the transaction     COMMIT      Terminate GO          

1 might ask what happens when you endeavor to execute DROP Procedure statement without an existing procedure? The following error bulletin is generated.

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

Adding Test Information to Our SQL Server Case

Before we talk about user divers views and functions, nosotros need to add test information to our database. I am not practiced at building model cars. With that said, wikihow had an commodity on the required steps to assemble whatsoever blazon of toy model. We volition use these steps every bit guidelines for creating our test data.

The T-SQL script below executes the post-obit actions.

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

/*    Clear tables */  -- Clear the tables TRUNCATE TABLE [TOY].[CURRENT_STEP] Get  TRUNCATE TABLE [Inspect].[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', i, '2011-04-01 00:x',  '2011-04-01 00:29', 'Wash all parts.', 1; GO  -- Stride 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:twoscore',  '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', 'Fleck paint off glue edges.', one; Go  -- Step half-dozen EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 01:40',  '2011-04-01 01:49', 'Test fit parts for problems', 1; GO  -- Step 7 EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 01:50',  '2011-04-01 01:54', 'Utilize correct amount of glue.', 1; Get  -- 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.', one; GO  -- Step ix EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', i, '2011-04-01 02:20',  '2011-04-01 02:29', 'Fill seams with body putty.', 1; Get  -- Step ten EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', 1, '2011-04-01 02:30',  '2011-04-01 02:39', 'Use paper while painting.', i; Go  -- Step 11 EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', one, '2011-04-01 02:40', '2011-04-01 02:49', 'Use white craft mucilage for clear pieces.', i; 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; Become  -- Step xiii EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', ane, '2011-04-01 03:00',  '2011-04-01 03:09', 'Inspect painting/gluing for touch ups.', ane; Become  -- Stride fourteen EXEC [TOY].[UPSERT_STEP_DATA] 'MUSTANG-1966-0001', i, '2011-04-01 03:x',  '2011-04-01 03:xiv', 'Sell the toy automobile.', 1; GO   /*    Testify the data */  SELECT * FROM [TOY].[CURRENT_STEP] Go  SELECT * FROM [Inspect].[HISTORY_STEP] Get          

The output below shows the examination 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 tabular array whose contents are defined by a query. The following list contains various means to put a view to good use.

1. Abstruse underlying tables and then that users can simply see sure columns.
2. Security can exist placed on view to restrict users.
3. Indexing a view makes it materialized (physical) for speed.
4. Provide backward compatibility for tables that have inverse.
5. Pre-compiled code that skips the query parsing phase.

Views can be used to provide business organization 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 tin exist used to pull the information into MS Excel for analysis.

In our hypothetical business instance, the business organization user wants a list of cars that are set to sell on EBAY.

The T-SQL lawmaking beneath creates a view named [TOY].[CARS_READY_TO_SELL].

/*    Create view  */  -- Sometime block of code IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =              OBJECT_ID(Due north'[TOY].[CARS_READY_TO_SELL]') AND type = N'V') DROP VIEW [TOY].[CARS_READY_TO_SELL] Become  -- New cake of code DROP VIEW IF EXISTS [TOY].[CARS_READY_TO_SELL] GO  -- Cars set 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 inquire what happens when you endeavor to execute Drib VIEW statement without an existing view? The following error message is generated.

Or, one might ask what happens when you endeavour 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 Driblet Role If Exists

User-defined functions are routines that take parameters, perform an action and render the effect of that activity equally a value. The render value tin can either be a single scalar value or a outcome prepare (tabular array). 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. Delight accept a expect at his article for total details.

We take been asked by the business line to create a office that returns the showtime fourth dimension, cease time and total duration for building a particular model. Our solution should exist a ITVF and then that we tin call information technology for a single model or apply it in a CROSS APPLY statement for all models.

The T-SQL lawmaking below creates the requested part named [TOY].[TOTAL_BUILD_TIME].

/*    Create function  */  -- Old block of lawmaking IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =              OBJECT_ID(N'[TOY].[CARS_READY_TO_SELL]') AND type = N'V') Drib FUNCTION [TOY].[TOTAL_BUILD_TIME] GO  -- New cake of lawmaking DROP Part IF EXISTS [TOY].[TOTAL_BUILD_TIME] GO  -- Total Build Fourth dimension - Inline TVF CREATE FUNCTION [TOY].[TOTAL_BUILD_TIME]      ( @MODEL_ID [varchar] (64) ) RETURNS TABLE As Render  (  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  Grouping 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 Drib Part statement without an existing role? The following mistake bulletin is generated.

Or, one might ask what happens when you effort to execute CREATE Part argument with an existing role? The following fault 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 primary purpose of the construct is to provide a simple proper 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 lot desire to use the default schema named [dbo]. This same supposition is used when crafting a SELECT statement without supplying a fully qualified table name.

The business users in the model toy visitor only have access to the [TOY] schema. Yous accept been asked to nowadays the [Audit].[HISTORY_STEP] for reviewing builds that accept bug. 1 way to consummate this asking without duplicating information is to create a SYNONYM.

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

/*    Create synonym */  -- Old block of lawmaking IF  EXISTS (SELECT * FROM sys.synonyms WHERE Proper noun = 'ALL_BUILD_STEPS' and      SCHEMA_ID = (SELECT schema_id FROM SYS.SCHEMAS WHERE Name = 'TOY')) Drop SYNONYM [TOY].[ALL_BUILD_STEPS] Get  -- New cake of code DROP SYNONYM IF EXISTS [TOY].[ALL_BUILD_STEPS] GO  -- Create a unproblematic synonym CREATE SYNONYM [TOY].[ALL_BUILD_STEPS]   FOR [AUDIT].[HISTORY_STEP];   GO    -- Prove the information SELECT * FROM [TOY].[ALL_BUILD_STEPS]; Go          

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

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

SQL Server Drib Type If Exists

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

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

/*    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) Non Naught;          

One might ask what happens when you try to execute Drop TYPE statement without an existing type? The post-obit mistake message is generated.

Or, one might enquire what happens when you lot effort to execute CREATE Type statement with an existing type? The following fault bulletin is generated.

SQL Server Drop User If Exists

What purpose does a database accept if there are no users to search and call back data? Azure SQL database allows you to define independent database users. There is no reason for logins to be divers in the master database unless the account is used for assistants. Microsoft is pushing for this design to guarantee higher SLA'due south with it's Platform As A Service offerings (PAAS). You may be required in the hereafter to switch to this pattern.

At this fourth dimension, I desire to give thanks 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 */  -- Quondam block of code IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'jkadlec' and blazon = 'S') DROP USER [jkadlec] GO  -- New block of code DROP USER IF EXISTS [jkadlec] GO  -- Create independent db user CREATE USER [jkadlec]   WITH PASSWORD='MS#sql*Tips', DEFAULT_SCHEMA=[dbo] Become          

Ane might ask what happens when you lot endeavor to execute DROP USER statement without an existing user? The following error bulletin is generated.

Or, ane might ask what happens when yous try to execute CREATE USER argument with an existing user? The post-obit error message is generated.

SQL Server Drop Office If Exists

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

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

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

/*    Create function */  -- Quondam block of code IF  EXISTS (SELECT * FROM sys.database_principals WHERE proper noun = N'mssqltips' and type = 'R') Driblet Function [mssqltips] Go  -- New block of lawmaking Driblet Part IF EXISTS [mssqltips] Become  -- Create user defined function  CREATE ROLE [mssqltips] AUTHORIZATION dbo;   GO    -- Add together user to part  Alter Office [mssqltips] ADD Fellow member [jkadlec];   GO  -- Del user from role Change ROLE [mssqltips] DROP MEMBER [jkadlec]; GO          

One might ask what happens when you try to execute DROP ROLE statement without an existing role? The post-obit fault message is generated.

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

More SQL Server Drop If Exists Statements

At the offset of this article, I stated that 5 of the 20 statements impacted by this new syntax are not used in mutual exercise by most developers.

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

1. DROP DEFAULT - This argument is deprecated and volition exist removed in the time to come. Utilise a DEFAULT constraint instead.
2. Drib Rule - This argument is deprecated and volition be removed in the future. Apply a Bank check constraint instead.
3. Drib ASSEMBLY - Common language runtime assembly is not support in Azure. Utilise for quicker algorithm execution with on-premises only version.
4. DROP Aggregate - Common language runtime aggregates are non support in Azure. Utilise for quicker algorithm execution with on-premises merely version.
5. Drib SECURITY POLICY - This statement is used with row level security. It is a brand new feature released in SQL Server 2016.

Summary

The new Driblet IF EXISTS optional clause has been added to xx 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 motorcar business was used equally a fictitious case written report to show examples for 15 of the 20 statements.

I propose yous start using this new syntax for scripts that will exist deployed on database engines with level thirteen.0 and above compatibility. If your scripts even so have to exist deployed on older database engines, proceed on using the older syntax.

Next Steps

• Read more tips on SQL Server 2016

Related Manufactures

Popular Manufactures

About the author

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

View all my tips

Article Concluding Updated: 2016-08-22

saavedrabatouth1961.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/4402/new-drop-if-exists-syntax-in-sql-server-2016/

Share this post