ALTER vs DROP/CREATE PROC - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ALTER vs DROP/CREATE PROC

Author

Topic

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-10 : 14:00:05

In http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73310 Brett says to drop and create procs rather than altering them.

If you drop and create you have to assign the permissions again so that would seem like unnecessary work and I can't see any downside to using alter, so Brett, or anyone else, please explain why you'd use drop and create on procedures in preference to alter?

I'm creating a new thread because my question has nothing to do with the original post.

Thanks

Kristen
Test

22859 Posts

Posted - 2006-10-10 : 14:11:07

We script the permissions for the SProc in the same script file.

That way we can create the SProc on another database (e.g. DEV then QA then PRODUCTION), and DROP/CREATE takes care of whether it already exists or not.

ALTER has the benefit of NOT overwriting the SProc if the replacement has any errors, but I don't think you can use ALTER unless it already exists, can you?

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-10 : 14:15:40

Droping it clears it from the cache, yes? and it will rebuild on first use?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-10 : 15:19:08

quote:
Droping it clears it from the cache, yes? and it will rebuild on first use?

Yes, but so does ALTER, so that's not a reason to DROP/CREATE.

quote:
We script the permissions for the SProc in the same script file.

Sure, if you are creating a whole script it would drop everything and create it again, but in cases when you aren't scripting all database objects, would you also drop/create - which is what Brett's pronouncement seemed to imply?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-10 : 15:27:06

We use ALTER all of the time. When we are finally ready for them to be moved to another environment, we script them out as DROP/CREATE.

Tara Kizer

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-10 : 15:42:21

if you drop/create, sysdepends can get messed up because then you have to worry about the order you create sprocs in.

I have a script for each sproc that contains this:

1. if not exists create a stub sproc that does nothing
2. alter proc with real code

Then run each script twice. that way sysdepends is guaranteed to be accurate, at least as far as the sprocs are concerned.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 - http://www.elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-10 : 16:07:08

HUH?

I drop and recreate so I know when the things was changed...since SQL soesn't have a modified date in the system tables...now there's a brilliant move

And how does sysdepends get screwed up exactly?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-10 : 16:18:47

That's what source control is for!

I believe 2005 keeps track of alters as well.

Tara Kizer

pootle_flump

1064 Posts

Posted - 2006-10-10 : 16:27:36

quote:
Originally posted by X002548

And how does sysdepends get screwed up exactly?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72818 - fifth post up from the bottom.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-10 : 16:38:58

I use drop/create so I can see the date created.

I don't worry about sysdepends, becasue I never use it.

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-10 : 16:40:39

Well we see the date created too as when we first create it, we have to use CREATE PROC. After that though, changes are tracked through source control.

Tara Kizer

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-10 : 19:28:02

quote:
Originally posted by X002548

HUH?

I drop and recreate so I know when the things was changed...since SQL soesn't have a modified date in the system tables...now there's a brilliant move

quote:
Originally posted by tkizer

That's what source control is for!

Uhm...I thought that was what code comments were for....

Alter procedure FooBar()
--blindman, 10/10/2006:  Added more foo, less bar.
.
.
.

I drop procedures when I don't want them. I create procedures when I need them. I alter procedures when I change them. Now THERE's some brilliant moves.

"Once you establish possibility, all that remains are desire and determination."

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-10 : 22:12:01

Well, that’s settled. Another SQLTeam consensus.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-10-11 : 01:55:00

jezemine:
"1. if not exists create a stub sproc that does nothing
2. alter proc with real code"

We considered this for just the reasons you state ...

"if you drop/create, sysdepends can get messed up because then you have to worry about the order you create sprocs in."

and that was our thinking, but I don't reckon it works sadly. And I don't think your "run twice" will fix it ... you would have to run in SYSDEPENDS order - which would be impossible for multiple circular references.

pootle_flump already posted the link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72818
but I would add that it was the post at 10/01/2006 : 02:00:04
rather than "5th post up from the bottom" which may have changed by the time you read this

Kristen

pootle_flump

1064 Posts

Posted - 2006-10-11 : 02:59:37

quote:
Originally posted by Kristen

pootle_flump already posted the link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72818
but I would add that it was the post at 10/01/2006 : 02:00:04
rather than "5th post up from the bottom" which may have changed by the time you read this

Lol.

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-11 : 09:58:08

Hell it's bad enough that sql server my use the wrong cached plan for a sproc even when you do a drop/create....I wonder how bad it would be with just alters

And dont me you haven't seen this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-10-11 : 14:06:20

So ALTER PROC doesn't throw away the cached query plan? That's scary!

Should I add an sp_recompile to my

DROP

CREATE

script - just in case?

Kristen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-11 : 15:14:54

quote:
Originally posted by Kristen

So ALTER PROC doesn't throw away the cached query plan? That's scary!

Yes it does, that's not a reason to DRP/CREATE, they both remove cached copies.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-11 : 19:54:24

quote:
Yes it does, that's not a reason to DRP/CREATE, they both remove cached copies.

Perhaps for you, I've had plenty of instances where neither one removes the plan from cache. Especially some older versions/service packs, they don't do it reliably if at all.

DROP/CREATE has a better chance of fixing (actually, abandoning) a broken sysdepends chain, but it does require all the procs to be done that way. ALTER won't do this, and will still generate a dependency warning if dependent procs don't exist. You gain a great deal of consistency by treating all your sproc, view, and UDF code as a single collection to be built/deployed all at once.

To each his/her own, but I prefer DROP/CREATE and including the permissions. You can't build an effective source control library with ALTER statements, and you need to include permission grants too (permission issues are not a reason to use ALTER only)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-11 : 19:58:34

I fully agree with you, Rob. All of our source control files have DROP/CREATE and permissions in them. In development though while in Management Studio or Query Analyzer, we typically do ALTER PROC. It's not until we do a build that we switch to DROP/CREATE (well that's done through the script wizard anyway and not by hand).

ALTERs are never part of our deployment scripts.

Tara Kizer

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-11 : 20:14:47

Wow, this thread has grown fast!

quote:
Originally posted by Kristen

"if you drop/create, sysdepends can get messed up because then you have to worry about the order you create sprocs in."

and that was our thinking, but I don't reckon it works sadly. And I don't think your "run twice" will fix it ... you would have to run in SYSDEPENDS order - which would be impossible for multiple circular references.

I didn't mean run each script twice in succession. Say you have two sprocs, spA and spB, and spA calls spB. Do this:

1. create spA stub if not exists
2. alter spA with real code (this generates a warning because spA calls spB, which doesn't exist yet, unless this is the second time thru)
3. create spB stub if not exists
4. alter spB with real code
5. goto step 1 (or exit if this is the second time thru!)

make sense? this way, sysdepends ends up being correct, and you don't have to worry about the order in which your scripts are run.

also, I keep all scripts in source control so I don't care about the "created" comment headers that sql server adds at the top of sprocs.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 - http://www.elsasoft.org

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources