Advantages of Stored Procedure in SQL Server

1- Reduce Network Traffic – It’s because when we create Stored Procedure our query gets stored in Database in Stored Procedure, This reduces traffic between client and server only call is send to the server to execute the store procedure and we pass only parameters value not entire SQL query, Therefore it reduces the network traffic.

2- Improve Performance- When first time you execute the stored procedure SQL Server Optimizer creates the execution plan and stores that execution plan in the cache so that whenever it will be called new execution will not be created and it will use the old execution plan stored in the cache memory. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

3- Once modified available to all clients – because it is saved in Database with a name and you are sharing the name not the definition of stored procedure so once you make any change in the SP, it will be always available for other users who has access on it. We can add one more advantage it is easy to maintenance.

4- Improve Security- When calling a procedure over the network, only the call to execute the procedure is visible. Therefore, malicious users cannot see table and database object names, embed Transact-SQL statements of their own, or search for critical data. It also helps to guard against SQL injection attacks because it is more difficult for an attacker to insert a command into the Transact-SQL statement(s) inside the proc. If user want then he can encrypt stored procedure definition so that malicious user can’t see Create Procedure definition.

5- Reuse of Code- Write your code once store in database and use it again and again, This eliminates needless rewrites of the same code.

6- Helps to ensure integrity of database.

Click here to know more about Stored Procedures in SQL Server,

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.