dynamic sql and indexes

Question

dynamic sql and indexes

fec

SSC Eights!

Points: 941
More actions
May 12, 2008 at 5:15 pm

#190494

If i have a dynamic sql query will it take advantage of indexes?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Providing there are indexes appropriate for the query, yes

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

...and provided that the syntax of the query hasn't made it impossible.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

fec SSC Eights! Points: 941 More actions · Answer 3

Thanks guys.

These forums rely on guys like you to share your wealth of knowledge.

- keep up the good work,

cheers,

Paul

bcronce SSCrazy Points: 2597 More actions · Answer 4

I haven't had to work with dynamic SQL yet, but I've heard some people mention that the execution plan can get messed up some times. You may want to see if this may be a future problem.

eg. Execution plan gets mad and decides to not use an index, may want a hint/etc..

I've seen some articles showing how to replace some dynamic SQL with stored procs so that there's less of an execution plan issue; although, quite limited on what it can replace.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

In my experience, Dynamic queries are less likely than compiled ones (i.e., sProcs) to do that.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]