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 to question. I answered: Execution Plan Retention and Reuse (Stored Procedure are compiled and their execution plan is cached and used again when the same SP is executed again)
Not to mention I received the second question following my answer: Why? Because all the other advantages are known (they are mentioned below) of Stored Procedure 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
You may like Why do we need use Stored Procedure?, How to Split comma delimited string into a Table in SQL Server
What is the best advantages of Stored Procedure in SQL Server
The article was published on August 31, 2014 @ 10:44 AM
Leave a Comment