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,


>I don't get it.
>Maybe I missing something very obvious or I didn't read this part of BOL, but almost same queries give me different results in execution plan.
>In one table I have PK clustered, in other I have regular index. When I execute first query execution plan shows INDEX SEEK when I execute second one execution plan shows TABLE SCAN.
>The version of SQL Server is obsolete. I test this on both 2000 and 2005, that is why I think I'm missing something.
>

>CREATE DATABASE Test
>GO
>USE Test
>GO
>Create TABLE Test1 (Fld1 int, Fld2 varchar(200)
>CONSTRAINT PK_Tets1
>    PRIMARY KEY CLUSTERED (Fld1)
>)
>
>Create TABLE Test2 (Fld1 int, Fld2 int, Fld3 int, Fld4 varchar(200))
>CREATE INDEX IX_Test2 ON Test2 (Fld1, Fld2, Fld3)
>GO
>
>--- This gives me INDEX SEEK for Test2
>SELECT Test1.*
>FROM Test1
>LEFT JOIN Test2 ON Test1.Fld1 = Test2.Fld1 AND Test2.Fld2 = 14 AND Fld3 = 1
>WHERE Test1.Fld1 > 0
>
>--- This gives me TABLE SCAN for Test2
>SELECT * -- Fields from both tables.
>FROM Test1
>LEFT JOIN Test2 ON Test1.Fld1 = Test2.Fld1 AND Test2.Fld2 = 14 AND Fld3 = 1
>WHERE Test1.Fld1 > 0
>GO
>USE Master
>DROP DATABASE Test
>