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?