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?

  • Your primary key is on i.
    – Jason B.
    Jul 13, 2016 at 20:47
  • 1
    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?
    – Pimenta
    Jul 6, 2020 at 17:14

1 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

  • Your are right Thank you for your help Chad.
    – Diogo
    Jul 13, 2016 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.