CODEWITHSUNDEEP
sqlsql-serversql-server-2000
Eric
Eric

Reputation: 3257

SQL Server Does Not Use Index

I have a Material table with two columns that are indexed.

CREATE NONCLUSTERED INDEX [MaterialName_PartType_idx] ON [dbo].[Material]
(
    [MaterialName] ASC,
    [PartType] ASC
)

CREATE NONCLUSTERED INDEX [PartType_idx] ON [dbo].[Material]
(
    [PartType] ASC
)

When I query 

SELECT PartType FROM Material

it uses Index Scan, but when I query

SELECT MaterialName FROM Material

it uses Table Scan.

SELECT MaterialName, PartType FROM Material

also uses Table Scan

Both PartType and MaterialName are of type VARCHAR(50).

Why would it not use Index Scan? I am using SQL Server 2000.

Upvotes: 0

Views: 99

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40319

Stock answer: it depends.

How wide are the columns? How "full" are they? How many columns are in the table? How many rows?

If there are just the two columns, and if Material is a varchar(100) that is often populated with 25 or more characters, and if PartType is an integer, and if there are thousands and thousands of rows [note that that is 4 ifs] then:

  • On selecting just the PartType, the SQL optimizer realizes that it'd be more efficient to read the index (4 bytes per row) than the table, and does so
  • On selecting Material, the SQL optimizer realizes that it's reading pretty much the whole table--no time saved reading the index. (I'm not entirely convinced by that, but I'm avoiding tossing in more IFs, such as are there other indexes, is there a clustered index, and so forth)
  • On selecting Material and PartType, the SQL same logic applies, only moreso.
  • A key factor, you do not have and order by clause--you're just asking for all the data in whatever order is most convenient for SQL to return it in. If you order by Material, odds are extremely good SQL will use that index.

Upvotes: 2

Related Questions