Why use a stored procedure?
Video: Why use a stored procedure?Now I'd like to talk about some of the advantages of using stored procedures. One of the primary advantages is to simplify the administration and maintenance for common routines. On a large database project we could have dozens of developers writing code to read and manipulate data in the same table. If each developer independently writes a simple INSERT statement for the table, there is a large chance someone will make an error. Also, if that table ever changes, every single developer has to go back and change their code.
- Next steps
Viewers: in countries Watching now:
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
- Comparing triggers, functions, and stored procedures
- Installing and configuring SQL Server
- Creating a stored procedure
- Returning data using data sets
- Creating user-defined functions
- Using "after," "instead," and nested triggers
- Modifying existing stored procedures
- Implementing logging on DELETE
- Choosing between T-SQL and CLR
- Executing a stored procedure
- Passing parameters
Why use a stored procedure?
Now I'd like to talk about some of the advantages of using stored procedures. One of the primary advantages is to simplify the administration and maintenance for common routines. On a large database project we could have dozens of developers writing code to read and manipulate data in the same table. If each developer independently writes a simple INSERT statement for the table, there is a large chance someone will make an error. Also, if that table ever changes, every single developer has to go back and change their code.
However, if we wrote one insert stored procedure and allowed all the developers to use that stored procedure, we gain some advantages. First, we reduce the chances of error. We also reduce the level of effort when a change is required. We can simply change the stored procedure. As long as the name of the stored procedure and the input parameters of the stored procedure stay the same, the developers will likely have no need to change their code. Another advantage is, with stored procedures, our code is stored with the data.
I typically have common task I regularly perform on a database. Rather than type in that code everytime I want to run it, I prefer to type it in once and save it for the next time. Certainly, I could create a code folder on my desktop and store all the scripts there. But I prefer to create a stored procedure. That way the code is stored in the database. Which reduces the chances the code will ever be misplaced, and it also provides the advantage of every time the database is backed up, all of my code is backed up. Stored procedures can improve our security and simplify the administration of security.
Using stored procedures we can easily provide different levels of access to different users. I often prevent any user from directly accessing any table. The only way they can read or modify the data is to utilize my stored procedure. This technique makes it very simple to prevent certain actions. For example, if I don't want a certain user to delete any data from a particular table, I can just deny them access to the delete stored procedure, or if I don't want anyone deleting data, I'd just never implement a delete stored procedure.
They won't have access to the underlying table. They won't be able to go there directly. And saving the best for last, the most important advantage of using a stored procedure is a performance improvement. To understand this performance improvement we'll have to talk a little bit about what happens every time you run code in SQL Server. If I just type in a query, when I go to run it, the machine performs several steps before it runs the query. First, the information is parsed.
Parsing means checking to make sure my syntax is good. I haven't misspelled a keyword or used a keyword that's invalid. Next, my code is algebrized and that means it changes object names to a consistent format. So for example, the categories table could be referenced just as categories or it could be referenced as dbo.categories, or we can even put the name of the database first. So with all of these different options on how to name things, the machine has to standardize all of the names in a consistent format before it can run it.
The third step is optimizing and optimizing the database decides which data structures to use. It will have to enumerate a list of all the tables to be read. It will decide which order it reads the tables in. It will look for indexes that could help in other optimization strategies. After the machine performs these three steps: parse, algebrize, and optimize, it results in an execution plan and it immediately executes that execution plan.
If I were to run this exact query again, it starts over from the top. It will parse it, it will algebrized it, it will optimize it, it will come up with an execution plan, and it will execute the execution plan. That is somewhat displeasing to me that it executed all those steps again. Apparently, someone at Microsoft had same feelings as me and stored procedures bypass some of this. So the very first time I run a stored procedure, it does go through all those steps. But when it creates the execution plan, it also saves the execution plan at that time.
So the next time I run the stored procedure it can skip the parsing, it can skip algebrizing, it can skip the optimizing. It goes directly to the saved execution plan and just executes that immediately. This can result in a significant performance improvement, typically in the optimizing stage is where we see the most improvement. If you have a large database with lots of indexes rather than the machine having to make a decision on which index to use every time, all of that can be saved in the execution plan, and we can see a significant performance increase from a stored procedure.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.