September 21, 2006 at 1:26 am
Hi All,
Can any body pls exlain the what the difference between batches and stored procedures in sql server 2000, in what way we are going to differentiate and what are the advantages of batches over stored procedures
Regards
Dinesh
Dinesh
September 21, 2006 at 12:44 pm
Batches are a whole serie of sql-statements sent at once by the client.
Stored procedure can contain a whole serie of sql-statements stored on the server.
Pro batchs: some flexibility (like search-commands) , circumventing the dba somewhat. Since they are recompiled mostly each time, they use the best query plan. Reduced networktrips since you sent all the statements at once.
Cons: Server has to regenerate a sql-plan instead of reusing the cached one (lesser with sql 2000 & 2005). It usually takes more bytes to send across the network when using multiple sql statements.
Pro stored procedures:
Extra blackbox layer. Callers don't need to know the internals of your database, only the input/output parameters and the expected results making deployment/patching easier since no reinstalls are required.
This also encourages reuse for multiple applications.
Generally less bytes sent through the network.
Lesser costs in cpu since the plan is mostly reused from cache.
Usually less prone for sql-injection since parameters need to match input/output parameters.
Extra security: users don't need permissions on the base tables if they are in the same database.
Easier maintance, only one place to look and you can use the sql server best practices analyzer on them.
If no dataset required, can make use of rapid RPC-handling
Cons: sometimes the cached queryplan isn't the best. Therefor there is a hint WITH RECOMPILE that forces the stored procedure to be recompiled each time. Ease of security lost when using dynamic sql inside stored proc. I'm trying to convince my developers to use stored procedures as much as they can. It allows me to add them to source-code-versioning (subversion), is easier to correct/tune (no reinstalls necessary on +50 pc's), lessens cpu-usage. One stubborn developer is still lazy enough to write select * in sql-batches.
September 21, 2006 at 11:57 pm
Thanks jo,
Regards
Dinesh
Dinesh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply