Thanks Kim,

With either of both indexes in your first set of 2 I see quick execution i.e. the good plan.

With either of both indexes in your second set of 2...

Anything that results in uniqueness, it seems.

You can even define the field to be UNIQUE in the table definition to get the effect.

Yes, there are at least 2 questions - 1. why the poor performance with adding a UNIQUE constraint and 2. why does switching from left join to inner join result...

Ben, six years later I seem to have the same issue you had with a UNIQUE constraint. My data consists of 2 tables, a view and a temp table,...

First, thanks to all who have taken an interest in this.

I've played around with this code. One observation - any form of UNIQUE constraint on the temp table results...

I've attached the 2 execution plans.

Hi again Eric,

Re the fragmentation - I've just tested this; it makes no difference performance-wise in the clustered index example whether the data is inserted before or after the index...

Thanks Eric,

I collected the stats with your expanded script. The 2 indexes are both reported as CLUSTERED with hid as the only column, 4318 rows and 0 reserved_mb.

As expected,...

Thanks Jeff,

If I leave the temp table as a heap it performs slightly better than with the clustered index, i.e. est. subtree cost 15.59 versus 17.65 on actual runs. ...

Thanks Kristen,

Yes, not precisely the same but in my mind (perhaps where the problem lies) functionally the same as the hid field is always unique, even if not declared so....

Thanks again Eric,

Yes, they have identical indexes; CLUSTERED with the same # of rows.

I am out of the office today; will get the very different execution plans to you when...

Thanks Eric,

Thanks for the script, I'll see what the results it gives.

The execution plans are totally different. That's the point really. Why should the execution plans be vastly...

Thanks Jeff,

The reason for this question is a perplexing performance problem. I am going to take some time to try to put it into compact, reproducible form but it...

Interesting. I would put maintainable ahead of performance if forced to choose. This sometimes leads me to break up a view with 'too many' joins into a small...