Should I drop and recreate a stored proc everytime?
-
When writing a stored proc is it best practise to check to check if the proc exists and dropping and recreating it if it does?
The question I have surrounding this is if I am dropping a proc and recreating it does that not force the query engine to recreate an execution plan each time and therefore losing the performance benefits of utilising stored procedures?
Thanks
-
I'm a bit confused... Are you writing some stored procedure that modifies another stored procedure and then runs it?
Or are you asking whether you should drop and recreate a procedure every time you want to run it?
If you're talking about development added to production, then I would recommend check/drop/create. But this only happens now and then, so you have no reason to be concerned about recreating the execution plan... Could you be more specific, please?
-
Interesting question I think.
While I see loads of scripts which first check "if exists then drop" I usually argue it's better to use ALTER in case an objects already exists, because you won't have to grant all the permissions again.
So my "best practise" would be NO, don't drop and recreate.
The point about "recreating execution plans" makes no sense because a new execution plan will be needed in both cases.
Markus
[font="Verdana"]Markus Bohse[/font]
-
Well, it probably depends on environment. If you write a script that will apply changes in a procedure to several databases (like when several clients use "copies" of the same database - meaning structure copies, not the same data), and want to use ALTER, it means you have to script it out twice. Once as ALTER and once as CREATE (in case the procedure does not exist for some reason). If you use DROP/CREATE, you only script the CREATE, but have to script permissions as well. I think it is more a matter of personal preference...
Of course, there is also a possibility to script just ALTER and throw error "Procedure missing!" if it does not exist, and then deal with it separately.
BTW, you can't see when the procedure was last altered, but you can see "date created". So, for possible future searches when precisely it was last modified, DROP/CREATE gives you the info you need.
I agree that the piece about execution plans is confusing, I didn't understand that either.
-
Execution plan may be changed even if nobody was touching SP for years.
It depends in statistics, on indexes, etc., not only on SP code.
If after next data upload statistics will be changed dramatically optimiser may decide that existing plan is not most effective any more and work out new one.
And keeping SP unchanged won't help here.
I use to say that optimising query performance starts with CREATE TABLE statement.
Code is important as well, but not always defining.
_____________
Code for TallyGenerator -
>> BTW, you can't see when the procedure was last altered, but you can see "date created". So, for possible future searches when precisely it was last modified, DROP/CREATE gives you the info you need.
Although true in SQL Server 2000, in SQL Server 2005 you can track when objects are modified.
-
Always alter - if the procedure does not exist, then create a "stub" that does nothing and then alter. If you drop/create then any security privileges will need to be included in the scriot and object dependency will get lost. Specifically, what other stored procdures execute this procedure.
IF OBJECT_ID('dbo.myprocedure') is null
create procedure dbo.myprocedure
as
return 0
end
alter procedure dbo.myprocedure
as
-- the real source
go
SQL = Scarcely Qualifies as a Language
-
My recommendation in SQL Server 2000 would be to always drop the proc so that you can easily tell, just by the CreateDate, that you have the correct rev especially in the absence of all other documentation (usually the case, no documentation). And, if you have Query Analyzer create the code from production before you alter it and Query Analyzer is setup for it, it will automatically generate the existing Grants.
On SQL Server 2005, shouldn't make a difference. But, I can see where folks might prefer alter over drop. Either way, not likely you'll break any code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oracle has the "CREATE OR REPLACE" syntax which I really liked.
Lacking that in SQL Server, I create each stored procedure or function as a stub. Then the main procedure or function file (in our source control system) contains an "ALTER" statement.
We "build" the main schema creation DDL script by taking multiple individual source files (from the source control system) and concatenate them together according to a "build" recipe. Having the file contain an "ALTER" means that we can use the same source file for both new schema builds as well as for upgrades to schemas in existing databases. I.e., you don't have to code it twice, test for existance, worry about grants and other issues, etc. As for modification timestamp, SQL Server 2005 has the "LAST_ALTERED" column in the information_schema.routines view.
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply