Script or Stored Procedure for db maintenance
-
We have so far used scripts to update our database. Update means adding rows in tables, altering tables, indexes etc. Someone gave me an idea that the same thing could also be accomplished with stored procedures instead. The advantages would be better error handling etc.
First, what are the limitations with SPs compared to script. Can I do everything in a SP that I can do with a script?
Second, are the any performance issues with this? What would be faster, script or procedure?
Thanks
Frede
-
Stored procedures will be faster than scripts bcoz when the stored procedures are executed once then their execution plan will be stored in the procedure cache.So there will be no need for re-compilation.But it will not be in the case of scripts.
..hema
-
Limitations in stored procedure:
1) Use 'set nocount on' at the beginning of the stored procedure and use
'set nocount off ' at the end of the stored procedure.
2) Don't use 'exec' while executing the stored procedure.Instead of that use
'sp_executesql'.
3)Don't use 'sp_ ' for user-defined stored procedures.
..hema
-
Stored Procedures are essentially a batch of T-SQL statements that are stored in the database. There are too many advantages to list here. Have a read of this topic in Books Online,
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_07_31vb.asp?frame=true
--------------------
Colt 45 - the original point and click interface
-
If your going to post things like this, you might also take the time to post the reasons
--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) -
yaah sure jeff,
1) If you specify 'set nocount on' then no messages will be sent to client
for eg. (1 row(s ) affected)..hence it reduces the network traffic.
2)You should not use 'exec' bcoz sql server
parses the query every time its executes..so it is better to use sp_executesql as sql server parses the query only once.
3)We should not use 'sp_' bcoz sql thinks it as a
system procedure and checks it in the sysobjects every time..
..hema
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply