Drop if exists considered harmful

by | Dec 16, 2014

Steven-Ensslen-Orange
I read lots of examples on the internet that check if an object exists, and then drop it before creating it. You’d be excused for thinking that this is good practise. It’s not.
Dropping an object has negative side effects:

  1. Fisher emphasizes that all of the GRANTs and DENYs are lost.
  2. Extended Properties are also lost when an object is dropped.
  3. SYS.OBJECTS has both a CREATE_DATE and a MODIFY_DATE. Maybe I’m just an old DBA, but I like to be able to see those date differ.

The correct thing to do is to use Visual Studio database project, or some other database release manager, like liquibase.
Unfortunately those tools don’t work well for short examples on the internet, and a variety of other examples we invent to take half measures. If you want to take a half measure the correct half measure is CREATE IF NOT EXISTS followed by an ALTER not DROP IF EXISTS followed by a CREATE.
I use the following when I need to distribute code without Visual Studio:

IF NOT EXISTS (
SELECT 1
FROM sysobjects
WHERE NAME = 'prc_SaveLog'
AND type = 'P'
)
EXEC ('CREATE PROCEDURE dbo.prc_SaveLog AS THROW 50001, ''UnImplemented'', 1;');
GO

ALTER PROCEDURE dbo.prc_SaveLog (@Parameter INT)
AS
BEGIN
PRINT ‘Do Something’;
END
GO

0 Comments
Submit a Comment

Your email address will not be published. Required fields are marked *