>I don't know if it will speed your query up. But since I have SET DELETED ON, I do expect to get the no optimization on the table that has no index on DELETED(). However, since this table has no deleted records, I suspect performance will not be affected with or without the DELETED() index. I would like to know what results you get on your very large tables with and without that index.

All main tables have index on DELETED(), which is not a good idea, but I can not change it right now. I seriously doubt, I gain anything by adding deleted() index in the temp table (it has not deleted records).

>>I don't think, this is the point (of course, I can add this index to this table, but I doubt, I gain from it). If I have something like
>>select * from SiteMstr inner join PropMstr on SiteMstr.PropID=PropMstr.PropID...
>>I belive, I had full optimization for both tables. In this case I have different name for Key field. Could this cause this problem? Also, ZipCode is not a foreign key for SiteMstr (as PropID), but just a regular field.
>>
>>I'll run couple of tests today to test...
>>
>>Ok, here is the test, I've ran now:
>>
>>sys(3054,11)
>>select tn1.* from towns tn1 inner join towns tn2 on tn1.town=tn2.town
>>
>>I see full optimization for both tables.
>>
>>With set deleted off I see none optimization for both. So, you're right, deleted() index will make a difference in SQL show plan. However, my question is about speed. Will it help to speed up the query?