Dynamic SQL - which would you use?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » Dynamic SQL - which would you use?

Add to briefcase

16 posts, Page 1 of 2

1 2 »»

Dynamic SQL - which would you use?

Expand / Collapse

Author

Message

Posted Thursday, January 03, 2013 8:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:32 AM
Points: 3, Visits: 21

When I need to execute code depending upon the values of parameters I often implement one of 2 methods:
In this method, I'm setting a boolean column, either by ID if it is provided, or if it is not then we're setting all records that are not already set.

Method 1: I have a different query depending on the parameter

IF @ID IS NULL
BEGIN
    UPDATE myTable 
    SET ClearFlag = 1
    WHERE ClearFllg = 0
END
ELSE
BEING
    UPDATE myTable 
    SET ClearFlag = 1
    WHERE ID = @ID
END

Method 2: I include the parameter in the query

UPDATE myTable 
SET ClearFlag = 1
WHERE 
	(@id IS NULL AND ID = @ID)
	OR
	(cleared = 0 AND @ID IS NULL)

For a simple query like this, either way probably works just as well. When multiple parameters need to be taking into account I consider that the first way maybe executes faster but if any changes are required then they must be made in all the 'copied' queries. The 2nd method may not execute as quickly (thought I've not really tested), it can result in some complex clauses but means there is only one query.

My question: Which way do you swing? Do you have any other ways? Any other comments welcome.

Post #1402401

Posted Thursday, January 03, 2013 8:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Neither. I'd use dynamic SQL.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1402405

Posted Thursday, January 03, 2013 8:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:32 AM
Points: 3, Visits: 21

I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.
I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

Interested to hear opinions.

Tony

Post #1402407

Posted Thursday, January 03, 2013 8:42 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

tony@kenny.net (1/3/2013)

I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.
I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

Interested to hear opinions.

Tony

Read the two articles Gail wrote and posted. She explains in detail about both of your types of queries and why dynamic sql will outperform either of the methods you posted. Smile

Smile

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1402417

Posted Friday, January 04, 2013 4:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:32 AM
Points: 3, Visits: 21

Hi,
Sorry, I misunderstood the links and thought they were just part of the signature so didn't give them any attention.

I've read the articles, thank you so much!
Looks like I was doing things right many many years ago with MS SQL 6 and my decision to change a few years ago was actually a bad move (at least for certain scenarios)!

Thank you for the great articles and for opening my eyes. I'm glad I got curious and asked!

best regards,
Tony

Post #1402807

Posted Friday, January 04, 2013 7:20 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 PM
Points: 727, Visits: 1,383

tony@kenny.net (1/3/2013)

I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.
I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

Interested to hear opinions.

Tony

IIRC, SQL Server can auto-parameterize a dynamic T-SQL query and cache the execution plan like any other. When future calls generate and execute the same dynamic T-SQL query, SQL Server will use the cached execution plan. So you could end up with separate plans for all possible variants of the dynamic T-SQL query, but as long as the execution plans remain in the plan cache, SQL Server will reuse them each time the same variants are executed.

Post #1402866

Posted Friday, January 04, 2013 7:51 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

wolfkillj (1/4/2013)

IIRC, SQL Server can auto-parameterize a dynamic T-SQL query and cache the execution plan like any other.

It'll always cache the plan, parameterised or not. Only very simple queries (no joins) qualify for auto-parameterisation. That said, the method in my blog posts uses a parameterised piece of dynamic SQL, meaning that queries of the same form with different parameter values will reuse plans.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1402887

Posted Friday, January 04, 2013 10:38 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

When I need to execute code depending upon the values of parameters I often implement one of 2 methods:

I see that you also confuse rows and records. Since SQL is declarative, the use of the T-SQL's procedural 4GL control flow should be very rare.

Have you ever had a basic Software Engineering course? Look up the terms “coupling” and “cohesion” in your unread text book. This is not SQL; this is much more fundamental. A module of code should have one and only one entry point, one and only one exit point, and perform one and only one task. The worst possible way to program is with bit flags that control the “Automobiles, Squids and Lady Gaga” module. The best way is to have highly coherent modules that are loosely coupled. Think of a math function.

Yourdon? DeMarco? Constantine? If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1403006

Posted Friday, January 04, 2013 11:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

CELKO (1/4/2013)

When I need to execute code depending upon the values of parameters I often implement one of 2 methods:

I see that you also confuse rows and records. Since SQL is declarative, the use of the T-SQL's procedural 4GL control flow should be very rare.

Have you ever had a basic Software Engineering course? Look up the terms “coupling” and “cohesion” in your unread text book. This is not SQL; this is much more fundamental. A module of code should have one and only one entry point, one and only one exit point, and perform one and only one task. The worst possible way to program is with bit flags that control the “Automobiles, Squids and Lady Gaga” module. The best way is to have highly coherent modules that are loosely coupled. Think of a math function.

Yourdon? DeMarco? Constantine? If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"

Jeez, Joe... get over it.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1403029

Posted Friday, January 04, 2013 12:56 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

CELKO (1/4/2013)

When I need to execute code depending upon the values of parameters I often implement one of 2 methods:

I see that you also confuse rows and records. Since SQL is declarative, the use of the T-SQL's procedural 4GL control flow should be very rare.

Have you ever had a basic Software Engineering course? Look up the terms “coupling” and “cohesion” in your unread text book. This is not SQL; this is much more fundamental. A module of code should have one and only one entry point, one and only one exit point, and perform one and only one task. The worst possible way to program is with bit flags that control the “Automobiles, Squids and Lady Gaga” module. The best way is to have highly coherent modules that are loosely coupled. Think of a math function.

Yourdon? DeMarco? Constantine? If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"

Devoid as usual of any actual recommendation.

What specifically should requestor do then?

What specifically then should one do when a table has, say, 50 columns that can be arbitrarily filtered by users in self-directed queries, and you have to provide the supporting code?

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1403078

« Prev Topic | Next Topic »

Add to briefcase

16 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse