Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2012-12-18 : 15:14:10
|
We used to use a IF EXISTS ... DROP / CREATE approach to creating Sprocs.I moved, some time ago, to IF NOT EXISTS (CREATE "dummy" SPROC)ALTER Sprocthe main reason being that if we had to upgrade/fix an Sproc "live" (i.e. without downtime) on a busy server this was far less intrusive than the DROP / CREATE method (it always amazed me that replacing an Sproc, which took only a couple of milliseconds, caused us a cascade of errors in the logs  ). I guess our servers/sites really are that busy!!The ALTER also leaves the original in place if the ALTER fails for some reason, and keeps the permissions.However, in large scripts there is a risk that an ALTER raises an error that goes unnoticed, amongst all the other "traffic", and then the old version is left in place causing a very hard to detect "submarine" bug :(On balance I think I would prefer that an Sproc that is re-created, but the create fails for some reason, "breaks" the application so that the fact that the re-create failed is Found & Fixed. (Plus we do have check-reports that determine that all expected Sproc names ARE in the DB)Obvious answer is that we should use DROP / CREATE for release scripts (which use scheduled downtime) and IF NOT EXISTS CREATE Dummy / ALTER for "live" fixes.But I really REALLY don't want to maintain multiple versions of the code, even if the change is only for specific releases. Introducing hybrids into the code-cutting process always bites me in the behind Which method do you use? any any opinions or Pros/Cons? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-18 : 16:22:18
|
| I always used ALTER for the reason that Kristen mentioned, namely the permissions remain intact. But, somehow I implicitly assumed that when I alter the stored procedure, it would cause the query plans to be invalidated (or not reused). If that is not the case, I have to rethink that strategy! |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 18:36:28
|
quote:
Originally posted by tkizer
For a traditional deployment, we shutdown the apps and use DROP/CREATE. If we are doing a one-off deployment with perhaps just a minor stored procedure change, then we use ALTER.
Yup, that has to be the right answer, thanks Tara. I think I'll just have to bite the bullet and change our process to use a different coding style when we do a "live" fix. My worry is that people will forget to change [some of] the code though, and running a DROP/CREATE in a live scenario will cause some users to get "SProc not found" type errors.By the by, I am as good as certain that using ALTER we have seen errors in the logs - i.e. client-users getting errors because the Sproc was not available - do you think that is possible? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 18:38:29
|
quote:
Originally posted by sunitabeck
I always used ALTER for the reason that Kristen mentioned, namely the permissions remain intact.
We are not reliant on that as we haveGRANT EXECUTE ON dbo.MySProc TO MyUserGroup in all our script files - so running a script to re-create any Sproc will also grant the appropriate permission. However, there is a delay between DROP/CREATE happening and the GRANT, which is no doubt going to cause run time errors for some connected users. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 19:55:14
|
| I'll run an ALTER on a core logging Sproc tomorrow and see if anything is recorded in the logs and report back here :) |
|
|
|
|
|
|
|
|
CREATE or ALTER to make Sprocs?