>>Thank you Walter,
>>although this is just an example I have the same situation with real data. And believe me both tables have records.
>>What surprises me is that if I make that index to be PRIMARY I have INDEX SEEK no matter what I use.
>
>You mean a *clustered* primary index?? If so, I can see that happening. A clustered index makes the data to be stored in the sequence off the index. The order of the data in the table actually *is* the clustered index.
>
>So, if you seek in a clustered index, the rest of the record is available too.
>If using a normal index only the indexed values are directly available. The SQL server has to look up the rest of the field values in the actual table. This is an extra step that costs extra I/O. The optimizer determines the cost of such a process and draws its conclusions and might pick a table scan and do a MERGE JOIN or HASH JOIN.
>
>so, In your case it probably is using a LOOP JOIN with a clustered index seek as that usually is the fasted method to do a LEFT JOIN or even an INNER JOIN.
>
>Walter,

No, no matter if PRIMARY KEY index is CLUSTERED or not in both cases I have INDEX SEEK.
Thanks again Walter.