In SQL Server 2016, Drop If Exists (DIE) is a handy new T-SQL language enhancement that eliminates the need to test before you drop.
So, if you need to delete a table, or a stored procedure, but you don’t know if it exists or not, then you’re used to writing code that says, “If the table exists, then drop it,” or “if the stored procedure exists, then drop it.” We’ve all been writing code like this for years, but that doesn’t mean it’s been fun:
• IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL DROP TABLE dbo.Product • IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trProductInsert') DROP TRIGGER trProductInsert
So now, thankfully, we can leave the testing to SQL Server:
• DROP TABLE IF EXISTS dbo.Product • DROP TRIGGER IF EXISTS trProductInsert
It doesn’t get much simpler than this. It’s really just an ordinary DROP statement; you just inject the new syntax IF EXISTS in the middle, and you’re done.
This new feature is available for just about anything you need to drop, so not just tables and triggers, but all of the following:
- SECURITY POLICY
So no, DIE isn’t really any major new big feature, but if you like neat code – as I do – then it’s welcome just the same.