Header_Footer

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:

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 !

 

 

Register for my SQL Server Performance Tuning Master Class!

Amit Bansal
SQL MCM, SQL MVP
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