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?)
posted @ Saturday, January 24, 2004 10:56 PM