Ozzie.eu

USING TEMPLATES WITH SQL SERVER MANAGEMENT STUDIO

Full of awesome indeed!

Picture: How to take a screenshot in 1983: http://t.co/kYg5bX4SF4

How to take a screenshot in 1983:

Why dynamic TSQL should be banned from Stored Procedures

The guys from SQLServerCentral.com published an article giving a Quick Tour of sp_ExecuteSQL. This is all very well, except for the antibodies in me regarding dynamic TSQL inside frequently used stored procedures.

Yes, it’s true that “Every once in a while you find yourself working with SQL Server and you need to execute a piece of T-SQL that will be  different at the time of execution from the time that the code is being written.” but pointing out SQL injection as the single concern is quite an understatement.

The key downside events regarding dynamic TSQL are statement compile time and execution plans. You can read on the official documentation at MSDN: “The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql.”

Furthermore “sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.”

One can try to optimize this dynamic execution by using parameter substitution, so the the Transact-SQL string is built only one time. But there are still aspects you can’t control:

• What is the generated execution plan for the dynamic statement, running separate from the execution plan of the batch that called sp_executesql;


• Is the execution plan any good? You can’t really tell to try to change it;


• Good or bad, the plan is cached for the following executions. If the diversity of queries that can dynamically be run is large, there’s no gain from the plan caching;


• Using the RECOMPILE option solves the above issue but creates another: each time the stored procedure is called, the execution plan is recompiled. For long running queries this might be overlooked, but for queries otherwise fast, it’s a major performance drawback.

Having stated my displeasure for the dynamic TSQL, I can also recommend the following readings:

• The Curse and Blessings of Dynamic SQL


• Is Dynamic TSQL a Good or Bad Idea?

In all applications I witnessed the development, using dynamic TSQL inside stored procedures caused nothing but trouble as soon as the tiny tables turned into a larger, business critical database with hundreds of users.