Ad-hoc / Raw SQL Vs Stored Procedures?

Download PDF – RawSQLVersusSP

Let’s go through some of the differences given below-

Compile – Well Ad-hoc SQL will be compiled every time we pass the query to the optimizer. But in case of stored procedures execution plan will be cached. One more point here is to we may need to recompile stored procedure from time to time. In any we have added indexes or statistics are updated then SQL server will recompile the stored procedure.

Please note that we have two types of recompilation – Statement level recompilation and procedure level recompilation.

Tuning and Future Usage in Data Retrieval Cases – Mostly I used to write stored procedures because they are easier for us to test and fine tune. If you want to change the stored procedure in future it is easy. You can just change it and deploy on the server required and test the application.

You don’t need to open the application, change the query and deploy it and after that you can test it. You can save lot of time in this case.

DBAs also like SPs as they can easily optimize stored procedures.

Future Usage in column addition cases – Now if you have an insert query and you wanted to add a new column to the insert statement then in stored procedure cases you have to change the SP as well as a change in required in the application tier to pass an extra value. So in these scenarios Ad-hoc will be better as you just have to change at one place.

Complex Operations / Performance – If you are performing some complex operations stored procedures are better as it will be very difficult to write complex operation at application tier as you have to use lot of concatenation for variables and other things.

In case of performance also SP are better. Add complex JOINS, couple hundreds of thousands records, and you will see SP advantage. Stored procedure by virtue of being compiled resolves all bindings at compile time (i.e. it won’t compile if you misspelled a table name) with in-line query, you find this only after compiling application and running test.

Security / SQL Injections – Stored procedures can be used to prevent SQL Injection attack, though they do not do it automatically. For example case of implicit conversion between data types: a stored procedure would allow for examining the parameters before executing them; think of it as of another level of protection.

Also stored procedure’s logic resides on the server, so its source code cannot be as easily accessed as by de-compiling desktop application. Stored procedure inside SQL Server is a securable, so it adds granularity to security schema.

Conclusion

So, which one to use – SPs or ad-hoc SQL? The answer is “it depends.” Personally I have used SPs most of the time as it has more performance and maintainability benefits