Dynamic T-SQL Resources
Finding good resources on working with dynamic SQL can be difficult. Many DBAs and developers ignore this powerful tool and choose to work with more cumbersome solutions.
Presentation Resources
From time to time, I give a presentation about dynamic SQL called “A Dynamic World Demands Dynamic SQL.” If you’ve seen that presentation before, this is where you can download the slides and a video I recorded of the presentation.
Online
Stored Procedures – Executing & Dynamic SQL – SQL Server Pedia has a short article about working with the EXEC statement and the sp_executesql system stored procedure. Understanding how both of these work is fundamental to working with dynamic SQL.
SQLQueryStress – SQLQueryStress is a tool developed by Adam Machanic that you can use to stress test queries and determine the frequency of query plan recompilation, amongst other things.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 – This whitepaper from Microsoft covers a number of the issues that can make dynamic SQL perform poorly: namely compile and recompile times as well as parameter sniffing. While this paper does not give any dynamic SQL specific solutions, it highlights the causes of these issues and provides tools and techniques to identify and solve these issues.
Automating T-SQL Testing – I’m shamelessly linking to myself here, but with good reason. I put together a T-SQL testing framework that makes heavy use of dynamic SQL. The benefit of this framework is that you can easily use it to run automated tests against dynamic SQL, especially when your dynamic SQL has optional parameters.
The Curse and Blessings of Dynamic SQL – This is probably the grandaddy of all resources on dynamic SQL. Erland Sommarskog works through many good coding practices for dynamic SQL and brings up security, formatting, and performance concerns and provides ways to address them.
Denis Gobo talks about the difference between exec and sp_executesql – A great resource discussing the differences between exec and sp_executesql. Denis provides some pretty exhaustive testing to back up his facts, too.
Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec – Denis Gobo once again provides great resources on how to avoid implicit data type conversions by using the right data type to begin with.
Books
Expert SQL Server 2005 Development – Chapter 7 provides a solid foundation for working with dynamic T-SQL.
Contributing
When I first started writing dynamic SQL, I had a hard time finding any resources on the topic. If you have any that you’d like to share send an email to jeremiah.peschka@gmail.com.
