Hi Friends,

I had started the 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 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:

 

1_SQL_Server_IndexInternals_Part2_Why_is_the_Optimizer_SEEKING_instead_of_SCANNING

 

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.

 

2_SQL_Server_IndexInternals_Part2_Why_is_the_Optimizer_SEEKING_instead_of_SCANNING

 

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???

 

3_SQL_Server_IndexInternals_Part2_Why_is_the_Optimizer_SEEKING_instead_of_SCANNING

 

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 !

 

Regards

Amit Bansal

Like us on FaceBook Follow us on TwitterJoin the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook