September 5, 2009 at 1:45 am
Hai Can anyone tell me in which scenario we should use stored procedure and when we should not use stored procedure in sql.
September 5, 2009 at 2:51 am
Hi,
It is a good practice to use stored procedure, even always.
Stored procedures offer several distinct advantages over embedding queries in your Graphical User Interface (GUI). Your first thought may be: "Why tolerate the added development overhead?" After seeing the advantages, you may change your mind.
Advantage 1: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.
Advantage 2: Stored procedures are tunable. By having procedures that handle the database work for your interface, you eliminate the need to modify the GUI source code to improve a query's performance. Changes can be made to the stored procedures--in terms of join methods, differing tables, etc.--that are transparent to the front-end interface.
Advantage 3: Stored procedures abstract or separate server-side functions from the client-side. It is much easier to code a GUI application to call a procedure than to build a query through the GUI code.
Advantage 4: Stored procedures are usually written by database developers/administrators. Persons holding these roles are usually more experienced in writing efficient queries and SQL statements. This frees the GUI application developers to utilize their skills on the functional and graphical presentation pieces of the application. If you have your people performing the tasks to which they are best suited, then you will ultimately produce a better overall application.
I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.
I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)
Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.
Execution plan retention and reuse
Query auto-parameterization
Encapsulation of business rules and policies
Application modularization
Sharing of application logic between applications
Access to database objects that is both secure and uniform
Consistent, safe data modification
Network bandwidth conservation
Support for automatic execution at system start-up
Enhanced hardware and software capabilities
Improved security
Reduced development cost and increased reliability
Centralized security, administration, and maintenance for common routines
Correct me if anything is wrong.
manikandan (9/5/2009)
when we should not use stored procedure in sql.
I would appreciate if somebody comment on this question.
Cheeers.:cool:
Ref: link1, link2[/url]
September 5, 2009 at 4:50 am
Hi manikandan,
You can find more detailed description regarding your question here with, including described by sudhanva.
1) http://searchsqlserver.techtarget.com/news/article/0,289142,sid87_gci1052737,00.html#
2) http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/[/url]
With the same, this is just a quick reply from myside with hope that it will help you; have a look at this,
http://forums.asp.net/t/1446216.aspx
"Don't limit your challenges, challenge your limits"
September 5, 2009 at 6:12 am
Excellent post sudhanva. Very well summarized. I agree, there are few, if any situations where stored procedures should not be used.
Unfortunately, I need to point out one issue:
sudhanva (9/5/2009)
...Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.
Execution plans are generated by just about every query that comes into the system, stored procedure or not. Those plans can be reused by ad hoc queries the same way as they are by stored procedures, assuming the ad hoc query matches exactly what was run before OR, the ad hoc query was called using parameterization. There a few ways a query can be parameterized. I'll focus on how you can do it through T-SQL.
If you send a query through sp_executesql it's possible to set parameters within the string that you pass to sp_executesql and then provide values for those parameters. This will ensure code reuse in exactly the same manner as a stored procedure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2009 at 9:37 am
manikandan (9/5/2009)
Hai Can anyone tell me in which scenario we should use stored procedure and when we should not use stored procedure in sql.
Yes... you shouldn't use a stored procedure to format data for a GUI or reporting system. That also includes many cross-tabs and pivots (not to be confused with aggregation). Keep the presentation layer and the data layer separate whenever possible... and it's usually possible.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2009 at 11:16 am
Jeff Moden (9/5/2009)[/b
Yes... you shouldn't use a stored procedure to format data for a GUI or reporting system. That also includes many cross-tabs and pivots (not to be confused with aggregation).
Can you explain this in detail, if possible with an example.
September 5, 2009 at 12:24 pm
Sure...
First, if you format dates or currency in SQL Server, you have to unformat it to do any math on the GUI side.
Second, if you format dates or currency in SQL Server, that's all you get. You're stuck with that format anywhere in the world the data may be displayed. If you do the formatting in the GUI, you can use the local regional setting of the client side to determine what the best format for display in that part of the world is.
Third, even though you can do some really great formatting in SQL Server, it takes clock cycles and the server is a limited resource. Do the formatting on the client side or in the web server to lighten the load on SQL Server.
Fourth, most reporting software and even gui software has it's own pivot/crosstab methods available. Again, think clock cycles... distribute all the formatting away from the SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply