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:
SELECT* FROM Person.Contact
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.
SELECT* FROM Person.Contact where ContactID = 1
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???
SELECT* FROM Person.Contact where ContactID BETWEEN 1 AND 18000
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 !