CODEWITHSUNDEEP
sql-servert-sqlindexing
Jamie
Jamie

Reputation: 268

Index Not Used When Field Selected

I've come across a weird situation where including a value in my select list determines whether or not my index is used in the lookup.

I have an index created on cTable(cf1,cf2,cf3).

In this first case, a table scan is performed on cTable:

select
    a.bkey
    ,c.mappedvalue
from
    aTable a
    LEFT JOIN bTable b
       ON b.bkey = a.bkey
    LEFT JOIN cTable c ON (
        c.[cf1] = b.[cf1] and
        c.[cf2] = b.[cf2] and
        c.[cf3] = a.[cf3]
    )
;

However when I remove the mappedvalue column from the select list, the index is used:

select
    a.bkey
    --,c.mappedvalue
from
    aTable a
    LEFT JOIN bTable b
       ON b.bkey = a.bkey
    LEFT JOIN cTable c ON (
        c.[cf1] = b.[cf1] and
        c.[cf2] = b.[cf2] and
        c.[cf3] = a.[cf3]
    )
;

Has anyone come across this? Is the optimizer just deciding to avoid the index?

Upvotes: 2

Views: 39

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175696

When you remove column then your index is covering index.

select
    a.bkey
    --,c.mappedvalue
from
    aTable a
    LEFT JOIN bTable b
       ON b.bkey = a.bkey
    LEFT JOIN cTable c ON (
        c.[cf1] = b.[cf1] and
        c.[cf2] = b.[cf2] and
        c.[cf3] = a.[cf3]
    )
;

You could add INCLUDE:

 CREATE INDEX idx ON cTable(cf1,cf2,cf3) INCLUDE (mappedvalue);

Then the query:

select
    a.bkey
    ,c.mappedvalue
from
    aTable a
    LEFT JOIN bTable b
       ON b.bkey = a.bkey
    LEFT JOIN cTable c ON (
        c.[cf1] = b.[cf1] and
        c.[cf2] = b.[cf2] and
        c.[cf3] = a.[cf3]
    )
;

should use index idx.

Upvotes: 2

Related Questions