Click Here for complete List.
In this post we will understand the advantages of using Stored Procedures over adhoc queries or inline SQL.
- Re-usability and retention of Execution plan.
- Reduces Network traffic.
- Better maintainability and code re-usability.
- Better Security.
- Avoids SQL Injection attack.
Let us discuss the above points below.
1.Re-usability and retention of Execution plan: When we issue a sql query, 3 things happen in SQL Server
- It checks the syntax of the query.
- Compiles the query
- Generates the execution plan for re-usability.
Execution plan: It is a way to retrieve the data in the best possible way from the database, and it depends on the indexes available on the table. Every time when we issue a query it will reuse the execution plan. This results in the performance boosts when stored procedures are executed again and again.
Now a days in latest versions of SQL Server, even adhoc queries will reuse the execution plan, but a small change in the sql query leads to generation the new execution plan, even if there a space in the query.
2.Reduces Network traffic: A stored procedure usually have sql statements in the body as shown below.
CREATE PROCEDURE ProcedureName
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Statements and Business Logic
They have enough number of lines in queries. I have seen 2000 lines of queries in stored procedure. When we have such kind of business need we can make use of stored procedures. In this case, simply we need to specify the name of the procedure and the parameter values over the network, all it transfers 3 words. If we have adhoc queries we have to send 2000 lines of code over the network.
3.Better maintainability and code re-usability: If the Stored Procedure resides on the server, several applications can use that Procedure, the advantage of doing so is if there is a bug in the Stored Procedure or the logic to be changed, there is only one place to change irrespective of used by multiple applications. If it is a inline sql every applicator has to change in each application which leads to complex process.
4.Better Security: In terms of fine grain control i.e., to control at lower level
For Example: A user needs a data from a specific table and it has all the employees information. let’s say one of the managers needs IT data, if we grant access to that user he can see all the employees records, which we don’t want. In those cases we can create a Procedure and grant execution on that Stored Procedure.
5.Avoids SQL Injection attack:
They avoid sql injection attack, From the user interface elements people can type sql queries and can inject sql if use dynamic sql for execution as shown below.
Consider the below example if we executing the queries in the database as shown below.
ShippingLocation = Request.form ("Location");
var sql = "select * from Employee where Location = '" + ShippingLocation + "'";
Assume that the user is prompted to enter the name of a Location. then query assembled by the script looks like the following:
select * from Employee where Location = ‘Hyderabad’
However, assume that the user enters the following statement:
Hyderabad'; drop table Employee–
In this case, the following query looks like this.
select * from Employee where Location = ‘Hyderabad';drop table Employee–‘
In SQL Server the semicolon (;) denotes the end of one query and the start of another statement. The double hyphen (–) indicates that the rest of the current line is a comment and should be ignored.
If the sql statement syntax is correct, it will be executed by the server, first select all records in Employee table where Location is Hyderabad. Then, SQL Server will drop Employee table.
Therefore, we must validate all user input and carefully review code that executes constructed SQL commands.
I hope this article is helpful to understand advantages of Stored Procedures in SQL Server.Please leave any feedback in the comments area. Happy Learning :)