Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered)

Question

I have the following tables and content

create table t(i int primary key, j int, k char(6000))
create index ix on t(j)
insert into t values(1,1,1)
insert into t values(2,1,1)
insert into t values(3,1,1) 
insert into t values(4,1,1)
insert into t values(5,1,1)
insert into t values(6,1,1)
insert into t values(7,1,1)
insert into t values(8,2,2) 
insert into t values(9,2,2)
select * from t where j = 1
select * from t where j = 2

I'm really confused on why the first SELECT uses only a Clustered Index Scan (Clustered) while the second one uses a Index Seek (NonClustered) and a Key Lookup (Clustered).

Execution Plans

Can someone clear this up for me?

• Jason B.: Your primary key is on i.
• Pimenta: I know this has been a while, but I'm getting this situation when creating an index proposed by Brent Ozar's SP_BlitzIndex and also from Azure portal recommendations. I have missing index warning and PK scans. So I create an index, it get's a lot of seeks but also generates PK lookups is this a bad thing?

Answer 1

Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria.

Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria. Once the query returns a certain percentage of the table (AKA The tipping point) the optimizer falls back to a Clustered Index scan, as that is the more efficient plan.

See Kimberly Tripp's blog post about The Tipping Point

• Diogo: Your are right Thank you for your help Chad.