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