To ALTER or to DROP/CREATE… That is the Question

Sat Jan 24, 2004 by Ajarn Mark Caldwell in best-worst-practices

Whether 'tis nobler in the CPU to suffer the slings and arrows of outraged users... oh, never mind. Back to the topic at hand which really is all about how you modify your SQL Server objects. Specifically, I'm going to talk about Views and Stored Procedures (a.k.a. sprocs). Tables are a completely different creature in regard to this topic.

It will come as no surprise to my readers that the first thing I'm going to tell you is to write a script to make the changes. Do NOT go to Enterprise Manager to make changes to a sproc! Just don't do it! It's a bad habit to get into, and once you've made the change and tested it in your development system (you DO test object changes in your development system before applying them to QA and production, don't you) then you'll need to make the same changes in your other systems and you never really know if you made exactly the same changes in each of the systems if you're doing it manually.

OK, so I've convinced you to write a script to make your changes. Great. Now back to the original topic... do you ALTER your sproc/view or do you DROP it and then CREATE it with the new definition? I prefer to DROP/CREATE, and here are a few reasons why:

I do a check for existence before I drop, and can therefore use the same script in a brand new database as I did in my development database.
SQL Server does not keep any stats on when an object was last updated or altered, but it does keep the date it was created. So if I ever care, it's there.
It just feels cleaner to me. I like the idea that I can grab the latest version, run it, and BAM! we're ready to go.

One drawback to this is that you have to reassign all the permissions for this procedure. With ALTER, the permissions remain intact. But this has not really been an issue for me because I save the GRANT statements as part of my script anyway (hey, what do you think the GO statement is for?)

Legacy Comments


Tara 2004-01-26	re: To ALTER or to DROP/CREATE... That is the Question I typically use ALTER in development environment. I use DROP/CREATE method in all other environments. DROP/CREATE is so much cleaner for QA and production environments since your script could get an error on ALTER if it doesn't exist. For development, it really is just a preference thing.

Lavos 2004-02-03	re: To ALTER or to DROP/CREATE... That is the Question Are you sure about keeping track of created/last altered? I seem to remember INFORMATION_SCHEMA.ROUTINES had a last_altered column in addition to created. I used to use them a good deal for finding new procs. I can't find a similar property for views, so I guess your point still stands :) Personally, I think the drop/create is better, but in development I tend to not bother and just right-click and edit from QA, and then generate the scripts later.

Kristen 2004-04-12	re: To ALTER or to DROP/CREATE... That is the Question We use IF EXISTS ... DROP and then a CREATE (as well as putting GRANT permssions stuff at the end of the script). However, this tends to muck up the DEPENDS and we are thinking of changing to IF NOT EXISTS ... CREATE dummy SP followed by an ALTER and the PERMISSIONS etc. The CRDATE in sysobjects is handy, but we have an EXEC xx_SP_LogMyScript('MyScriptName', 'MyVersionNo') at the top of all our scripts that kinda soes the same thing (and gives a history and the ability to compare version numbers between database instances