It's always said that we should prefer stored procedures in SQL other than ad hoc queries. But why should we do that? In this post, I'm going to focus on the reasons for that.
Have you ever wondered what happens in the background when we query 'Select * From Table'? I bet you didn't. Then, let me explain what happens. In SQL, every ad-hoc query is processed through 6 different steps. And these are the steps:
The first step is the query. In this step, we send our query to SQL by using a tool. This tool can be SSMS, Excel, etc... The second one is called Parse. During Parse, SQL checks if our query is syntax error-free or not. The other thing that it does is access control. It checks if we have permission to access the table. Optimize is the part where SQL decides which index to use, by checking the statistics of indexes. When we click on 'Display Estimated Execution Plan' we can see which index SQL choose. During Optimize, SQL always chooses the index which is going to be the most cost-effective. Compile is the step where SQL converts the query to the language that the computer understands. Execute is the executing part of the query via connecting to the server, reading the data, and taking it to the memory. And lastly, Result is the step where the data is sent from the server to the client. These are the steps that SQL executes during ad-hoc queries. What about stored procedure steps?
When we use stored procedures, the first 4 steps are already executed and their results are stored. The first advantage of using a stored procedure is better performance, and this is the reason why. Stored procedures work faster than ad-hoc queries because those 4 steps aren't executed every time we query. They are executed when we create the stored procedure and kept in the memory.
Another advantage of stored procedures is that they are safer from SQL injections. SQL injection is the placement of harmful code in SQL statements via web page inputs. They might destroy everything you have in your database. One of the best ways to avoid those injections is using stored procedures. If we use an ad-hoc query, we can still avoid injections with extra steps. But with stored procedures, we don't need anything extra.
The other thing that I like about stored procedures is that they are safer when it comes to authorization issues. We can give access to critical data on stored procedure base. For example, a user may not have an access to a table, but when we give access to that user for a procedure that uses that table, our user can see the related info on that table.
Also, we can execute one stored procedure inside another stored procedure. The advantage of this is better performance. When we call one stored procedure inside another one, our stored procedure works faster.
The last advantage of it that I'd like to mention is we can see the statistics of stored procedure. For example, if you'd like to know when was the last time the stored procedure executed, or how many times it was executed, or to see CPU usage statistics, sys.dm_exec_procedure_stats is the stored procedure for that.
When we execute this stored procedure, we get more than 30 columns with statistics of procedures. Click here to see what those columns represent on Microsoft's documentation page. It's very likely that we have many stored procedures in our database. To understand which stored procedure SQL is showing us, we need to enhance our query a little bit.
There is a chance that you are going to exec this procedure and see nothing. It's pretty normal because they are deleted after some time. If you know how to keep them for a longer time, let me know in the comment box.
Comments