I was reading through some of the enhancements introduced with SQL Server 2016 and I stumbled upon an interesting addition. The T-SQL construct introduced has been something very common and on the pending request for years I suppose.
So what are am I talking about? I do a number of demo’s for conferences and I have shared a number of script for it. One of the script you will find starting of any of my such demo is to drop any objects that were inadvertently left from my previous run.
-- Typical Script IF OBJECT_ID('[Person].[CountryRegion]', 'U') IS NOT NULL DROP TABLE [Person].[CountryRegion]; -- Another variation of the Drop script IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_PersonAddress') DROP TRIGGER trg_PersonAddress
I am sure this is nothing strange. Now with SQL Server 2016, the same script can be rewritten as:
-- Updated Script with SQL Server 2016 DROP TABLE IF EXISTS [Person].[CountryRegion]; DROP TRIGGER IF EXISTS trg_PersonAddress;
It was a pleasant surprise to see this construct added to SQL Server and I thought to bring this to you as soon as possible. The best part is, if the object doesn’t exist, this will not send any error and the execution will continue.
This construct is available for other objects too like:
AGGREGATE | SCHEMA USER |
PROCEDURE | DEFAULT |
TABLE | SECURITY POLICY |
ASSEMBLY | VIEW |
ROLE | FUNCTION |
TRIGGER | SEQUENCE |
VIEW | INDEX |
RULE | TYPE |
DATABASE | SYNONYM |
As I was scrambling the other documentations like ALTER TABLE, I also saw a small extension to this capability. Now consider the following table definition:
USE tempdb GO CREATE TABLE t1 (id INT IDENTITY CONSTRAINT t1_column1_pk PRIMARY KEY, Name VARCHAR(30), DOB Datetime2) GO
Now I can use the following extension of DROP IF EXISTS like:
ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_column1_pk; ALTER TABLE t1 DROP COLUMN IF EXISTS ID;
As I try to wrap up, how cool is this for you? Will you be using them? Do let me know.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
It is really nice to see we can write ‘IF EXISTS’ in to the one statement, now need to check how it’s write, if we selected Create script for Drop and Create.
Thanks
Sanjay Khot
I agree @Sanjay
Very happy about this… this is already available in pgSQL and is one of the few things I prefer in pgSQL to t-SQL.
I am with you on this.
May I just say “hallelujah! and “About time!”, and “Thank you!” in equal measures :)
Did they add “CREATE TABLE IF NOT EXISTS”?
Another cool addition would be if they allowed multiple tables in a single statement, ex. DROP TABLE IF EXISTS dbo.Table1, dbo.Table2
I don’t think they added “CREATE TABLE IF NOT EXISTS” . But we don’t need that if DIE works well :)
Thanks Pinal, this new feature will be a great help..
Yes. I also think the same.
Thanks Pinal.It is useful for guy like me.I often forget the old syntax.Now it is so easy to remember and look clean.