June 26, 2006 at 6:04 am
hi,
could someone help me understand the difference between index seek and index scan. I need to know how a data is fetched in seek and how it is in scan?
Thank You,
Ashok Jebaraj
June 26, 2006 at 8:17 am
Seek and scan are as the word inplies.
Lets say we want to look up the word "book" in a dictionary.
Seek is like reading the top header of a dictionary, you go right to the b's, then right to the bo's, then right to the word book.
but a scan would begin at A. and review every word on every page until you reach the word book.
Seek is always faster than scan on tables of any significat size.
on small tables a scan may be faster than a seek.
June 26, 2006 at 11:20 pm
Thank you for the response.
Now say you have a clustered index in a table called Customers with CustID as PK. Does seek and scan on Customers table start from the root node of the clustered index or only the leaf nodes of the index. How does SQL server choose from where to start the search?
June 27, 2006 at 8:12 am
I do believe the seek starts from the Non Leaf, or Root pages, reading the headers, and a scan looks throu the Leaf pages. hence why it takes so much longer
June 27, 2006 at 1:17 pm
You might want to check out this article on Scan vs Seek:
http://blogs.msdn.com/craigfr/archive/2006/06/26/647852.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply