DROP IF EXISTS (DIE) – New in SQL Server 2016
Guys, welcome to the “New in SQL Server 2016” series. In this post, I will be discussing “DROP IF EXISTS (DIE)” command.
DROP IF EXISTS (DIE): Small but convenient language enhancement. This is mainly used when need to drop an object to test before its existence before deleting it i.e. CONDITIONAL DROP STATEMENT.
IF OBJECT_ID ('dbo.student','U') IS NOT NULL
DROP TABLE dbo.student
From SQL Server 2016 CTP3 DIE can be used
DROP TABLE IF EXISTS dbo.student
If the object does not exist, DIE will not fail and execution will continue. Currently following objects can DIE:
- AGGREGATE
- ASSEMBLY
- DATABASE
- DEFAULT
- INDEX
- PROCEDURE
- ROLE
- RULE
- SCHEMA
- SECURITY POLICY
- SYNONYM
- SEQUENCE
- TABLE
- TRIGGER
- VIEW
DIE can also be added on columns and constraints in ALTER TABLE statement
ALTER TABLE DRROP COLUMN IF EXISTS
ALTER TABLE DROP CONSTRAINT IF EXISTS
In the upcoming posts, I will be discussing –
- TRUNCATE TABLE WITH PARTITIONS
- SESSION_CONTEXT
- DYNAMIC DATA MASKING
- ALWAYS ENCRYPTED
- SQL SERVER 2016 QUERY STORE
- Stretch DB
- TEMPORAL DATA
Article by – Kanika Mehra