GilaMonster (10/16/2012)

---

The index is covering, so one seek operation to the start of the range and a partial scan is all that's necessary.

It's not traversing the index 9.5 million times. If it was, the logical reads would be ~27 million. It's traversing the index tree once, reaching the start of the range of rows that match then reading along the index leaf level until it's got all the rows. It's more efficient than a scan as it doesn't have to read the (few) rows that don't match.

p.s. Please post new questions in a new thread in future. Thanks

Thanks Gail

That makes a lot of sense.

Are there instances when cardinality does play a part in the optimiser's choice of 'scan vs seek' in the presence of a covering index and all SARGable predicates?

Thanks again

Andy

No.

The only reason cardinality can flip a seek to a scan of the cluster is because of the cost of the key lookups. A covering index doesn't need key lookups and so there's no reason at all to switch to a more expensive scan.

p.s. can you post your PM and my reply in the forum thread, for anyone who encounters it via google