Is Dynamic TSQL a Good or Bad Idea?

What are the benefits and drawbacks of using dynamic TSQL?

[In an effort to help seeding this site].

more ▼

asked Oct 11, 2009 at 02:08 AM in Default

avatar image

Mitch Wheat
292 1 4 7

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Erland Sommarskog's article, The Curse and Blessings of Dynamic SQL, is often cited as the canonical reference.

more ▼

answered Oct 11, 2009 at 02:10 AM

avatar image

Mitch Wheat
292 1 4 7

(comments are locked)
10|1200 characters needed characters left

It depends on how you implement dynamic sql what the different benefits and drawbacks are. Do you mean adhoc SQL created on the client or dynamic SQL created in a stored procedure based on parameters passed in? Are you going to use Exec (@sql) or sp_executesql to execute the dynamic SQL?

Some benefits may be:

  1. Flexibility - you can return any columns from any table with any where clause, any grouping, and any ordering.
  2. Performnance - you can get a better execution plan than doing control of flow and having many different Select's, especially if you use sp_executesql as you get parameterized queries and will more likely get plan re-use.

Some drawbacks may be:

  1. Security - depending on your implementation you open yourself to SQL Injection and you may have to grant direct access to the tables.
  2. Complexity - it can make your implementation more complex, harder to read and debug. If you are using stored procedures you may choose to use the EXECUTE AS clause or signed modules to keep from having to grant direct table access.
  3. Performance - if you do not use sp_executesql then you will likely not benefit from plan re-use and will have more activity in the plan cache as items will be inserted more often and aged out sooner.

Erlaand does a good job in the article referenced and is usually what I would reference.

more ▼

answered Oct 12, 2009 at 03:54 AM

avatar image

Jack Corbett
1.1k 3 4 7

(comments are locked)
10|1200 characters needed characters left

I think that Dynamic SQL is much maligned. It has a small place in many systems, and shouldn't be a first choice to solve a problem, but I prefer to choose generating dynamic SQL within the database (perhaps in a stored procedure) than allowing it to be used from outside the database. Its use should always be justified.

Because every problem falls into different categories, I would say that the places I have used and justified dynamic SQL are:

Dynamic PIVOTs - the PIVOT construct does not allow for a dynamic number of columns to be generated. In this case, I often generate the PIVOT from the data or metadata in order for the code to require no maintenance.

Table comparison - I have a table/view comparison tool which uses the metadata about two tables/views and creates and executes SQL dynamically to analyze the differences between the tables, including options for what to consider to be keys and thresholds for data differences allowed in numeric columns.

When looping through a variable set of tables, perhaps to append them all to a single table, it is not possible to do this in straight SQL, and generating such dynamic SQL outside the database is overkill, and actually riskier.

A technique I tend to use a lot are code templates:

SET @sql = REPLACE(REPLACE(@template,

This allows the SQL to be more readable and maintainable in one place instead of throughout many concatenations.

In all cases, dynamic SQL generated from metadata can be made extremely safe from SQL injection - typically they are only vulnerable if a column name is pathological.

more ▼

answered Oct 12, 2009 at 10:30 AM

avatar image

Cade Roux
98 2 2

(comments are locked)
10|1200 characters needed characters left

Dynamic SQL is a blessing and curse.

If a limited set of options are available, and inputted values are used as variables where possible, or (where variables won't apply) first checked for validity against system views (such as sys.objects or sys.columns), then it can be quite safe.

If the variety of queries that can be run is large, then the query hint OPTION (RECOMPILE) should be considered, as there is no gain from having the query plan stored in the cache.

more ▼

answered Oct 22, 2009 at 12:24 AM

avatar image

Rob Farley
5.8k 16 22 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 11, 2009 at 02:08 AM

Seen: 10234 times

Last Updated: Oct 13, 2009 at 09:00 AM

Copyright 2018 Redgate Software. Privacy Policy