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

About these ads

37 thoughts on “SQL SERVER – Stored Procedures Advantages and Best Advantage

  1. 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?

  2. 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)

  3. 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

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

  5. 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.

  6. What are the disadvantages of stored procedure

    except This?

    stored procedure may hinder the performance

    if it is excessively recompiled

  7. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  8. Hi,

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

    Waiting for your kind reply.

    Thanks in advance.

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

    • @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).

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

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

  9. 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?

  10. 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

  11. If we pass the query to stored procedure and just write a simple exec statement to execute that query, do we still can get benefit of using stored procedure?
    Example:
    create procedure mySP
    @qry varchar(max)
    as
    exec (@qry)

    and @qry is passed through the application code like:
    string qry = “select * from myTable”;

    Thanks.

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s