Getting back with you my new blog with DIE. 🙂 Sound interesting & crazy at same time. Yes, SQL Server 2016 is getting to us with a new T-SQL clause DROP IF EXISTS, in short (DIE).
Traditionally we all, irrespective of holding developer, DBA or DEVOPS profile very much used to of IF EXISTS statement to check the existence of the object(s), if the object exists following statement starting with “DROP”. SQL Server 2016 has come up with jamming those two different statements together as “DROP IF EXISTS”. Definitely going to help you in squeezing code lines, perhaps not much however good to have feature.
IF EXISTS check the existence of an object and based upon return value (TRUE/FALSE) IT DROPS associated object or not. This statement will avoid writing of condition and within if condition writing a statement to check the existence of an object.
Let me give example here how it works traditionally vs in SQL Server 2016.
-- Traditional Script IF OBJECT_ID('[HumanResource].[Department]', 'U') IS NOT NULL DROP TABLE [HumanResource].[Department] -- Another way of Scripting to achieve same goal IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'Department') BEGIN DROP TABLE [HumanResource].[Department] END
in SQL Server 2016
-- DIE in SQL Server 2016 BEGIN DROP TABLE IF EXISTS [HumanResource].[Department] END
Another benefit of DIE clause it can easily jam with ALTER Statement. Here is quick example
ALTER TABLE DIE_TESTING DROP CONSTRAINT IF EXISTS DIE_ID_PK1; ALTER TABLE DIE_TESTING DROP COLUMN IF EXISTS DIE_NAME;
I’m using this new statement here and there with caution, still verify twice (/perhaps multiple times) before shooting into PRODUCTION environment. 🙂