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.
>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?
Mark McCasland
Midlothian, TX USA