SQL SERVER – Stored Procedures Advantages and Best Advantage

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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Script to Find SQL Server on Network
Next Post
SQL SERVER – Fix : Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

Related Posts

43 Comments. Leave new

  • 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://blog.SQLAuthority.com)

    Reply
  • Marco Antonio C. Santos
    April 19, 2007 4:04 pm

    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?

    Reply
  • what is difference between sql server 2005 and sql server 2008

    Reply
    • in sqlserver 2008 you have new datatypes like date,time,datetime and geografy .
      in geografy you can sotore the videos

      Reply
  • 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

    Reply
  • i want what is the diffrent between sql 2000 and 2005
    any one able to telme

    Reply
  • Yes, the difference between SQL 2000 and SQL 2005 is “5”.

    Reply
  • Valter Vigati
    July 16, 2008 6:56 pm

    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!!!!

    Reply
  • 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.

    Reply
  • What are the disadvantages of stored procedure

    except This?

    stored procedure may hinder the performance

    if it is excessively recompiled

    Reply
  • Is it TRUE OR FALSE

    execution plan is not cached in case of functions(udf)..?

    Reply
  • hahaha….Great reply Lee…

    Reply
  • Vivek Jain
    May 6, 2009 5:09 pm

    Hi,

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

    Waiting for your kind reply.

    Thanks in advance.

    Reply
    • Hi,

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

      Waiting for your kind reply.

      Thanks in advance.

      for this u have to use top in the query i.e(“select top 2 field name from table _name)
      for more info go to w3schools.com,,,,,

      Reply
    • For this

      you can try
      select Top 2 Field Name from Table_Name
      or
      select Top 2 * from Table_Name

      Thanks

      Reply
  • Imran Mohammed
    May 7, 2009 8:38 am

    @Vivek

    Select top 2 * from Table_Name

    ~ IM.

    Reply
    • @Imran

      Good answer (as usual from you).

      I’d like to suggest a line from the help file: “Parentheses that delimit expression in TOP is required in INSERT, UPDATE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this. ”

      So, TOP(2).

      Reply
  • Cool One…………

    Reply
  • Could any one explain what is the difference between T-SQL and PL/SQL ?

    Reply
    • SQL is the Structured Query Language the ANSI/ISO Standard database language. SQL Server’s implementation of the language is called Transact-SQL (T-SQL).

      Reply
    • @madhav

      PL/SQL is Oracle’s implementation of the SQL language.
      T-SQL is Microsoft’s.

      The differences are too many for a blog reply. I suggest you get some documentation on each if you need to know them both. They are, of course similar, as they adhere to the SQL standard.

      Reply
  • Could any one explain what Query auto-parameterization is?

    Reply
  • Hi Friends

    Pls help me..,

    I know stored procedure is used for reuse and fast execution.But my Question is How is do fast Execution? and How it is fast from inline query?

    Reply
  • ganesh joshi
    July 23, 2011 11:22 am

    very nice article……..

    Reply
  • pls solve my problem i want to know that how i can insert records in alter column or newly added column in sql server 2005

    Reply
    • When you add new column to the existing table, you need to write update statement to update the values to that column

      Reply
  • Hi mate,

    Its 5 years since you blogged this post. Does these still make sense in the light of newer versions of SQL Servers?

    Thanks,
    Chris

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version