x

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

Mitch Wheat gravatar image

Mitch Wheat
292 1 1 3

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

4 answers: sort newest

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

Mitch Wheat gravatar image

Mitch Wheat
292 1 1 3

That is the best reference I have found. To summarize it, there are some things which are effectively impossible to do without some form of dynamic sql (running a given query against an arbitrary table for instance). But when you have a choice between using dynamic SQL or not, then not using it is almost always the correct choice.
Oct 20, 2009 at 12:18 PM TimothyAWiseman
(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

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

(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 @template = 'SELECT {@COLUMN_LIST} FROM {@TABLE_NAME}'
SET @sql = REPLACE(REPLACE(@template,
    '{@COLUMN_LIST}', @COLUMN_LIST),
    '{@TABLE_NAME}',  @TABLE_NAME)

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

Cade Roux gravatar image

Cade Roux
98 1

(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

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x986
x51

asked: Oct 11, 2009 at 02:08 AM

Seen: 5474 times

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