Why table scan?

[bborissov]

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.

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

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[mrdenny]

It scans the table, because you are asking for it to return a column which isn't in your index. So SQL decided to read the table instead of the index as it will need to go back to the table and read those data pages from disk anyway to get the Fld4.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bborissov]

OK,
But WHY if that index is PRIMARY I have INDEX SEEK, no matter if it is CLUSTERED or not.
With this:

Create TABLE Test2 (Fld1 int, Fld2 int, Fld3 int, Fld4 varchar(200)
CONSTRAINT PK_Tets2
    PRIMARY KEY (Fld1, Fld2, Fld3)
)

I get INDEX SEEK for:

SELECT *
FROM Test1
LEFT JOIN Test2 ON Test1.Fld1 = Test2.Fld1 AND Test2.Fld2 = 14 AND Fld3 = 1
WHERE Test1.Fld1 > 0

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

I am getting a seek for both

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

  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Test1].[Fld1]))
       |--Clustered Index Seek(OBJECT:([Test].[dbo].[Test1].[PK_Tets1]), SEEK:([Test1].[Fld1] > 0) ORDERED FORWARD)
       |--Index Seek(OBJECT:([Test].[dbo].[Test2].[IX_Test2]), SEEK:([Test2].[Fld1]=[Test1].[Fld1] AND [Test2].[Fld2]=14 AND [Test2].[Fld3]=1) ORDERED FORWARD)

 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

  |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([Test].[dbo].[Test2]))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Test1].[Fld1]))
            |--Clustered Index Seek(OBJECT:([Test].[dbo].[Test1].[PK_Tets1]), SEEK:([Test1].[Fld1] > 0) ORDERED FORWARD)
            |--Index Seek(OBJECT:([Test].[dbo].[Test2].[IX_Test2]), SEEK:([Test2].[Fld1]=[Test1].[Fld1] AND [Test2].[Fld2]=14 AND [Test2].[Fld3]=1) ORDERED FORWARD)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

With no PRIMARY KEY index for Table2?
Only with IX_TABLE2?
Grrrrr,
Then I am totally confused.
Then it should be some Database or SQL Server setting I'm missed.

Thank you for your efforts.
I really appreciate them.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP