Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-09-24 : 09:15:48
|
| Hi all,When I use EXEC() to execute a dynamic query , will the index in the WHERE clause of the dynamic query would be used ?Thanks,Hariarul |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 09:52:56
|
| Yes, no change from executing the EXACT SAME query by any other means.HOWEVER, that is not the same as the Query Plan being cached, which is probably the biggest downside of using Dynamic SQL in general, which is probably what you are doing with EXEC.Kristen |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-24 : 10:48:11
|
| > HOWEVER, that is not the same as the Query Plan being cached...what do you mean by that, Kristen?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 11:04:32
|
| EXEC(... some dynamic sql ...)will cache the dynamic SQL, but the likelihood is that the dynamic SQL being created is "unique" - e.g. with embedded string-constants, rather than @Parameters, so it is unlikely that that exact same query will be reissued, and thus the cached query won't be reused.I know it is increasing less the case nowadays, but dynamic SQL created using, say, sp_ExecuteSQL that is fully parameterized with @Variables will be more likely to have a cached query plan that is reusedKristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-24 : 12:03:15
|
| in ss2k yes.in ss2k5 no.ss2k5 parametrizes all cached queries._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 12:06:38
|
"ss2k5 parametrizes all cached queries"Indeed. But its a bit like "Shall I bolt some existing blocks together and create a huge application tat Moore's law will take care of in 18 months" (c) Microsoft  or "Shall I optimise it so it will always be faster than any other method"There has got to be cost in discovering thatSELECT * FROM MyTable WHERE MyName = 'Kristen'is already in the cache asSELECT * FROM MyTable WHERE MyName = @P1And all that optimising junk only exists to help dynamic SQL writers. Nothing wrong with that, per se, but that doesn't mean that parameterizing the SQL in the first place isn't a better route, all things considered.But I'm open to debate on it.Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-24 : 12:10:20
|
| no of course not there's nothing to debate about._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-24 : 12:14:17
|
Damn, I was hoping to learn something new that was going to make me a fortune  |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-24 : 12:16:20
|
| truly sorry to burst your bubble _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
|
|
|
|
|
Index usage in dynamic queries ?