SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 CREATE or ALTER to make Sprocs?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/18/2012 :  15:14:10  Show Profile  Reply with Quote
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 Sproc

the 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

USA
37466 Posts

Posted - 12/18/2012 :  15:44:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 12/18/2012 :  15:45:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
I prefer DROP/CREATE since you get a new execution plan, and that code change could have impacted the plan. You could recompile it, but I don't like relying on people remembering to recompile it or adding that to the script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/18/2012 :  16:22:18  Show Profile  Reply with Quote
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!
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/18/2012 :  18:36:28  Show Profile  Reply with Quote
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?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/18/2012 :  18:38:29  Show Profile  Reply with Quote
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 have

GRANT 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.

Edited by - Kristen on 12/18/2012 18:38:56
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 12/18/2012 :  19:33:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Kristen


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?



I don't think that's possible, but you never know. I would guess that SQL does a schema lock or similar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/18/2012 :  19:55:14  Show Profile  Reply with Quote
I'll run an ALTER on a core logging Sproc tomorrow and see if anything is recorded in the logs and report back here :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000