Register now or log in to join your professional community.
Procedures are a great tool to build & maintain objects & data in database ,select complex query, apply data business roles. it gives you the ability to control in database like programming language (using the conditions and iterators), and gives you accessing to SQL Server ready functions & proceders.
Reduces the load on the client end so basic spec device is enough in the client end
Less network usage
Stored Procedures is a fundamental programming structure in relational database system like MS SQL Server and provides many advantages like:
Performance - SPs are precompiled code and hence several stages of query execution are already done and save time. Reduce network traffic as we have to do a single line code call for a SP which may contain thousands of code lines. SPs also has ability to re-use optimized cached plans.
Security - SPs encapsulate code and hence hides backend list of objects from end application. SPs can also be stored in encrypted format to hide proprietary code from end users and administrators.
Reduced server/client network traffic, Stronger security,Reuse of code, Easier maintenance, Improved performance
Stored procedures provide improved performance because fewer calls need to be sent to the database. For example, if a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.
First, SQL Server has already parsed, optimized, and compiled stored procedures, so they run quickly without needing to repeat these steps each time. Also, stored procedures run on the SQL Server, using the power of the server and reducing the load on the client (which might be a much less powerful computer). Finally, using stored procedures reduces network traffic. Instead of the SQL Server sending all the data to the client and having the client run a query, the client sends a request to the server to run the procedure. The server returns only the result data set, which is usually a lot smaller than the full data set.
Once a stored procedure has run, it remains in memory, so the next user can run it without incurring the overhead of loading it into memory.
We can use stored procedures to enforce a level of consistency in your client applications. If all the client applications use the same stored procedures to update the database, the code base is smaller and easier to maintain, and you run less risk of deadlocks because everyone is updating tables in the same order.
Stored procedures enhance security, too, because you can give the users only EXECUTE permission on the stored procedures, while restricting access to the tables and not allowing the users any direct update privileges. When other users run a stored procedure, they run it as if they had the permissions of the user who created the query.
Stored procedures provide improved performance because fewer calls need to be sent to the database. For example, if a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.
The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:
1) It provide layer of security.
2) Easily maintain and reusable.
3) Improve performance
1. reduce time and effor.
2. minimize the code.
In order to reuse the code
1. To reuse a block of code.
2. Minimize coding complexity
Store procedure having bunch of code.. It will be reusable... aslo its works faster than normal query.
