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).

Can someone clear this up for me?

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