Dynamic SQL

Dynamic SQL consists of building SQL statements in strings and then executing them with either the EXECUTE() command or the system stored procedure sp_execute(). Generally, it is recommended to not use dynamical SQL as it can easily impact performance if not done properly. However, there may be applications where it cannot be avoided or a few cases where it is actually recommended (see the section on stored procedure recompiles). The following website is an excellent resource on the pros and cons of dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html.

From a performance perspective, one of the main issues with dynamic SQL relates to how SQL Server manages its execution plans. Every query and stored procedure run in SQL Server requires an execution plan, which basically represents the strategy SQL Server is using to search for and retrieve data. When the code is run for the first time, SQL Server builds an execution plan for it (for example, it ‘compiles’ the query) and the plan is saved in cache. The plan is reused until it is aged out or it is invalidated for some other reason like the query or stored procedure code is changed and this is where problems start to occur with dynamic SQL. As dynamic SQL typically involves changing the structure of a query, the execution plan is not reused and must be recompiled each time. The time SQL Server takes to generate an execution plan can be significant so it can result in significant performance degradation.

This performance issue can be partially alleviated through the use of the sp_execute() stored procedure as it allows the definition of parameters in the dynamic SQL which reduces the amount of query modification and allows the plans to be reused. As such, if using dynamic SQL, it is especially important to use sp_execute() in place of the EXECUTE() statement. However, modifying the columns returned and/or the WHERE clause itself may still result in recompiles.

It is a best practice to always use a variable to hold the SQL statement. For example,

DECLARE MyString nvarchar(4000)
SELECT MyString = ‘SELECT * FROM MyTable’ 
EXEC sp_executesql @MyString