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

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

    Reply
  • its very nice thanks please update it regularly

    Reply
  • nice article

    Reply
  • why don’t you put sql tutorials on youtube. that will helps every one please

    Reply
  • Hi Pinal Dave ..Can you please explain how stored procedure can help in Network bandwidth conservation

    Reply
    • If you have 200 lines of query to be executed 50000 times a day, you could have two options:
      1. Make your application make database connection with those 200 lines of code.
      2. Put those 200 lines in a stored procedure, and just call the stored procedure by name.

      From database perspective, though outbound data (from the database to application) size will not have any difference, the inbound data will have significant less amount of “network packets”.

      Reply
  • hi this is my question how to short out the lack of records in single table

    Reply
  • how to compare deleted data and insert data in sql server

    Reply
  • hi so good sir

    Reply
  • advantage of store procedure

    Reply
  • From what I know, execution plans are stored for adhoc queries too.

    Reply

Leave a Reply

Menu