SQL SERVER – Stored Procedures Advantages and Best Advantage « Journey to SQL Authority with Pinal Dave

SQL SERVER – Stored Procedures Advantages and Best Advantage

April 13, 2007 by pinaldave

There are many advantages of Stored Procedures. 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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 14 Comments

14 Responses

on April 19, 2007 at 4:04 pm Marco Antonio C. Santos

Thanx Pinal Dave for contributions to ColdFusion(firstly) and (by now) SQL Server communities! But only a tip: for us, ColdFusion(and PHP, ASP, JSP, Perl, Ruby, and so on) developers, Stored Procedures pre-compilation doesn’t help because our dynamic applications will always sent different parameters, ya? Do you have tests about?
on April 19, 2007 at 4:16 pm pinaldave

Thank you Marco for your words.

If we pass different parameters to Stored Procedure like
EXEC SPName @DifferentParam
it will still give you benefits of Pre-Compilation as SQL already expects the dynamic param there so the plan is cached and next time it runs faster. SP does not provide benefits when dynamic SQL is generated in the SP.

Again, thank you for stopping by and saying hello!

Pinal Dave
(http://www.SQLAuthority.com)
on July 26, 2007 at 11:28 pm kaja

what is difference between sql server 2005 and sql server 2008
on September 11, 2007 at 5:10 am vivek

hi,

im in need of a sp to delete a particular table from the database and restoring the same table from a .dat file .. actualy its a concept of scheduling it has to be done everyday

looking for ur reply..

Thanks,
vivek
on November 14, 2007 at 12:00 pm suresh

i want what is the diffrent between sql 2000 and 2005
any one able to telme
on July 7, 2008 at 8:06 pm Lee

Yes, the difference between SQL 2000 and SQL 2005 is “5″.
on July 16, 2008 at 6:56 pm Valter Vigati

Pinal Dave,

I have a system using Database “DBF.” Develop a program that makes the load of data updated in the “DBF” to SQL 2005, using the Advantage Database Server to make the reading of the data “DBF.” I have had some problems in reading this data. You could report some knowledge about this case?

Thanks!!!!
on July 17, 2008 at 12:27 am aviral

With the optimal usage of Prepared Statements even qury plan caching can be obtained so I feel the ability to tune and modular coding is the best advantage of stored procedures.
on October 4, 2008 at 10:59 am rakesh

What are the disadvantages of stored procedure

except This?

stored procedure may hinder the performance

if it is excessively recompiled
on October 4, 2008 at 11:03 am rakesh

Is it TRUE OR FALSE

execution plan is not cached in case of functions(udf)..?
on February 24, 2009 at 5:37 pm SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Stored Procedures Advantages and Best Advantage [...]
on March 13, 2009 at 8:26 pm Rahul

hahaha….Great reply Lee…
on May 6, 2009 at 5:09 pm Vivek Jain

Hi,

I have a query, how to select top two records from the table?

Waiting for your kind reply.

Thanks in advance.
on May 7, 2009 at 8:38 am Imran Mohammed

@Vivek

Select top 2 * from Table_Name

~ IM.