Please watch Part 18 - Basics of Stored Procedures
The following advantages of using Stored Procedures over adhoc queries (inline SQL)
1. Execution plan retention and reusability - 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. Reduces network traffic - You only need to send, EXECUTE SP_Name statement, over the network, instead of the entire batch of adhoc SQL code.
3. Code reusability and better maintainability - A stored procedure can be reused with multiple applications. If the logic has to change, we only have one place to change, where as if it is inline sql, and if you have to use it in multiple applications, we end up with multiple copies of this inline sql. If the logic has to change, we have to change at all the places, which makes it harder maintaining inline sql.
4. Better Security - A database user can be granted access to an SP and prevent them from executing direct "select" statements against a table. This is fine grain access control which will help control what data a user has access to.
5. Avoids SQL Injection attack - SP's prevent sql injection attack. Please watch this video on SQL Injection Attack, for more information.
Sir, can you explain 4.Better Security Point in detail. Such as how to give grain access control
ReplyDeleteHello There!
DeleteI think you should refer SQL injection topic. Because if you use inline queries in the application and suppose taking values from textbox, then user may use injection techniques to fetch data through provided input control. However SPs provide more secured way extracting data. point 4 &5 are in relation to each other.
-Pankaj
I have one question regarding Execution plan retention and reusability, if there is a "Select * from table" statement in the Stored procedure. Execution plan is cached and used again? What if a new column is added in the table, Execution plan will change or use the cached one?
ReplyDelete- Ajit
Since u r not altering the stored procedure.. the Execution plan may be reused again.. Correct me if i am wrong
Deletevenkat your videos are awesome to learn but i feel it would have been more useful if you give a exercise after every tutorials you perform.
ReplyDeleteChanges made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
ReplyDelete