Using Index scan instead of seek with lookup

Question

I have a table with the following structure:

CREATE TABLE Article 
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    title VARCHAR(60),
    content VARCHAR(2000),
    datePosted DATE,
    srcImg VARCHAR(255),
    location VARCHAR(255)
);

I then put a non clustered index on location:

CREATE NONCLUSTERED INDEX Articles_location
ON Articles (location);

Running a query like this one:

select a.content 
from Articles a 
where a.location = 'Japan, Tokyo';

results in an: "Index Scan (Clustered)"

Running another query like this :

select a.location 
from Articles a 
where a.location = 'Japan, Tokyo';

results in an: "Index Seek (NonClustered)"

So the nonclustered index is working. Why is it not doing a seek with lookup when I search by additional by columns but does a scan?

• The total number of rows in the table is 200
• The total amount of rows retrieved is 86 for this query

Answer 1

It looks like the query optimizer decides to scan the table instead of using an index based on the selectivity of the data.

It may be actually faster to refer to the table directly than to seek via the index and then perform a KeyLookup. This may not be the case if table has more rows (> 10k). Here 86 from 200 is more than 40%.

select a.content from Articles a where a.location = 'Japan, Tokyo';
-- clustered index scan

select a.location from Articles a where a.location = 'Japan, Tokyo';
-- covering index

---

Scans vs. Seeks

Thus, a seek is generally a more efficient strategy if we have a highly selective seek predicate; that is, if we have a seek predicate that eliminates a large fraction of the table.