Advantages of stored procedures


This is a very common sql server interview question. There are several advantages of using stored procedures over adhoc queries, as listed below.

1. Better Performance : Stored Procedures are compiled and their execution plan is cached and used again, when the same SP is executed again. Although adhoc queries also create and reuse plan, the plan is reused only when the query is textual match and the datatypes are matching with the previous call. Any change in the datatype or you have an extra space in the query then, a new plan is created.

2. Better Security : Applications making use of dynamically built adhoc sql queries are highly
susceptible to sql injection attacks, where as Stored Procedures can avoid SQL injection attacks completely.


3. Reduced Network Traffic: Stored procedures can reduce network traffic to a very great extent when compared with adhoc sql queries. With stored procedures, you only need to send the name of the procedure between client and server. Imagine the amount of network bandwith that can be saved especially if the stored procedure contains 1000 to 2000 lines of SQL.

4. Better Maintainance and Reusability: Stored procedures can be used any where in the application. It is easier to maintain a stored procedure that is used on several pages as the modfifcations just need to be changed at one place where the stored procedure is defined. On the other hand, maintaining an adhoc sql query that's used on several pages is tedious and error prone, as we have to make modifications on each and every page.

If you can think of any other advantage of using stored procedures, please contribute by submitting the form below.

7 comments:

  1. SQL Server creates a cache entry for the execution plans of Stored procedures when they are first run. On subsequent runs, if the execution plan matches, this provides a performance advantage.

    ReplyDelete
  2. With stored procedures, you can make changes to data retrieval operations at any time without affecting users or having to re-distribute your application code. For example if you have a query with a "where..." clause and you need to change the criteria, just modify the stored procedure (from SSMS), make the change, run the update code and you're back in business. With text-based queries from your application, such as linq, you need to republish or redistribute your application!

    ReplyDelete
  3. Hi Venkat,

    Can you post examples, it will be good to understand..

    ReplyDelete
  4. Since, SQL Security permissions can be placed on SPs, they are more secure.

    ReplyDelete
  5. Another Advantage of using Stored Procedure is to
    Avoids SQL Injection Attacks

    ReplyDelete
  6. Stored Procedure is Compiled block of code. It compiles once till we alter the sp and we can execute it again and again. But when we use SQL Queries it compiles every time and then executes. So SQL Queries takes more time compare to SQL SP. So SP is better than SQL Queries for performance.

    ReplyDelete
    Replies
    1. Please how it compiles once ,let me know with one real word example as I am new to it

      Delete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us