Hi Friends,

I had started the SQL Server index internal series sometime back. The first part is here;

Apologies for making it late on Part 2 – this part is more about some food for thought in SQL Server index internals. So here is the question:

In AdventureWorks database, there is a table called Person.Contact and there is a clustered index on COntactID. So here are the scenarios:

When you issue out the following query, the optimizer rightly does a clustered index scan:


When you issue out the following query, the optimizer rightly does a clustered index seek. The output gives you one record, which means SQL Server has done one seek.


But when you issue out the following query, it seeks again. The output returns 17995 records. Does it mean that SQL Server is seeking 17995 times???


I hope the scenario is clear. I am looking for answers from all of you. Let me know what you think. Why is the optimizer SEEKING instead of scanning, as you know, in this case, scanning could be faster. Awaiting your responses !



Learn SQL & related technologies from top-notch experts. 3 days. 60+ speakers. 100+ sessions. Join Asia’s first SQL Conference.

Amit Bansal
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Browse my blog posts on SQLMaestros
FB page | Connect on LinkedIn | Google+ | Know more about me

SQL Server - Spatial fun with Mondial database
SQL Server - Speaking at SQLBits - March 2012