Advantages and Disadvantages of Stored Procedures

Stored procedures are special Sql server objects which will be stored in database.

Advantages of  stored procedures:

  • Script re-usability: Script complied once can be reused as many times as needed.  
  • Execution Plan Retention and Re-usability:  Execution plan will be stored in the database for a stored procedure and same plan will be executed when ever a stored procedure runs.
  • Encapsulation of complete business logic in a unit:  Stored procedure encapsulates the complete process business logic inside the stored procedure. Instead of executing all the queries, it’s easy and safe to execute the stored procedure.
  • Safety and Security: Stored procedure can be granted with access. This will allow only intended resources to utilize or run the stored procedure. Security feature in stored procedures shield the user from directly accessing the tables in a database.
  • Easier to call from external application:  Stored procedures can be easily invoked from external applications like C#.net , Asp.Net and web services.
  • Client- Server traffic will be reduced by using stored procedures since this will use lesser bandwidth compared to the queries.
  • Temporary stored procedures are also supported in SQL Server. This feature will allow stored procedures to be created in temp db just like temporary tables. That is # and ## stored procedures can be created in SQL SERVER. They will automatically be dropped when you disconnect from the server.
  • Simple and Easy to use: When a process needs to be executed or invoked from some external application, we need not transfer all sql queries involved in the process. Instead we can just call the stored procedure.

Disadvantages of stored procedures:

  • Execution plan of the stored procedure is cashed.  Same Execution plan will be used and executed whenever a stored procedure is executed. Some times old execution plan may not be using the latest indexes. In those cases execution plan will not be correctly optimized. This will hinder the performance of the stored procedure.

This entry was posted in . Bookmark the permalink.

Leave a reply