>Hi Boris,
>
>First of all your example below of course does not contain any records. The Query plan might be very different depending on the data statistics.
>
>What I think is happening here is that in the first query you only check for the existance of a matching record in Test2. SQL server is smarter than VFP in the way it uses indexes. Since the information can be retrieve from the index alone, it does not have to touch the test2 table at all.
>
>In the second query, you request the actual field contents of test2, and therefor SQL server has to retrieve the actual information from the table. Whether SQL server is doing a Table scan or index seek, really is depended on the calculated cost (SQL server has a cost base optimizer, VFP has a rule base optimizer). Since you example does not contain any records, it is doing a table scan. If these table contain thousands of records, SQL server is examining the query, the keys and their selectivity and draws a execution plan that is the most efficient.
>
>
>Walter,

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.