Here's the official MS answer :

Hi, Thanks for your feedback. The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER...

I don't really see how much testing this would require...

SET @CI = 'CREATE NONCLUSTERED INDEX #IX_TmpTable_spName ON #Temp ('

if @parm 1 IS NOT NULL or @Param2 is not null

begin

SET @CI...

Just an idea... are you able to conditionnaly choose which index to build depending on what conditions are set to be used in the query?

Here's the idea...

create index on PK...

Sure PM me as well!

Still rolling on the floor on that one... can't be more true than that!!!

Sure you can go with the "cheap" one... but look at the # of posts answered and see for yourself who is better qualified for the job :D.

I need some help here... how can the same amount of data sent on the wire require less resources, less work on the 2nd run... all that because of the...

Can you explain to me why the network traffic would change for the exact same result set... having the exact same amount of data?

I know that query is about 30...

That code makes no sense to me... maybe I'm missing the point.

In any case, if you were using the inserted table in the trigger, you would have no problems if...

I see the value in all those good tips... but since almost all my triggers do an inner join on the inserted and deleted tables... the fact that no rows...

I know what you mean, I used to take all those comments as they weredirected straight at me some time... forgetting the nature of the boeard :)... but that's...

Somebody deleted some posts because that message is not making any sense now!

Thanks for the correction Jeff... you clearly said what I meant silently :P.

A 5 row triangular join won't kill many servers.... but what if the report needs to return the...

Again about the 10%/30%, I was thinking about another version of the script that I wanted to implement...

The query of the dm views runs in around 2 minutes (over 50...

... of course the cost of this query would be quite more considerable (maybe 2 or 3 times) if this was a real table with 1000 bytes of data and...