November 1, 2010 at 11:09 am
Context of question(s):
SQL Server, the clustered table with only clustered index (CL_IX), no other indexes.
"Introduction to Indexes (By Gail Shaw)"[/url] tells:
"Scans
An index scan is a complete read of all of the leaf pages in the index. When an index scan is done on the clustered index,
***it’s a table scan in all but name.***
When an index scan is done by the query processor, it is always a full read of all of the leaf pages in the index, regardless of whether all of the rows are returned. It is never a partial scan.
A scan does not only involve reading the leaf levels of the index, the higher level pages are also read as part of the index scan."
The last bolded part seems to me in contradiction with "it’s a table scan in all but name".
From this citation I can understand that Full CL-IX (Clustered Index) Scan is always bigger (more involved/consuming) than Full Table Scan. The latter should be just equal to scan of only leaf levels (data) of CL_IX, i.e. Full CL-IX Scan minus scan of intermediate levels of it.
1)
Correct?
2)
In presence of any index, including CL-IX, the Full Table Scan is never done. Correct? But why?
3)
Why to have Full CL_IX Scan if Full Table Scan is smaller in size (and seems to me, less complicted)?
I feel I miss key points how/why/when it is used and what it really constitutes.
AQny explanations are welcome.
I asked already similar question in "Why/when/how is whole clustered index scan chosen rather than full table scan?" but I cannot further clear there my doubts since stackoverflow.com is not discussion (but question and answer board) and I already had been banned there over a dozen of times for starting "discussions" there (well, in ServerFault.com).
November 1, 2010 at 11:40 am
vgv8 (11/1/2010)
The last bolded part seems to me in contradiction with "it’s a table scan in all but name".From this citation I can understand that Full CL-IX (Clustered Index) Scan is always bigger (more involved/consuming) than Full Table Scan. The latter should be just equal to scan of only leaf levels (data) of CL_IX, i.e. Full CL-IX Scan minus scan of intermediate levels of it.
1)
Correct?
2)
In presence of any index, including CL-IX, the Full Table Scan is never done. Correct? But why?
I think the main problem is a bit of context. As soon as you create a clustered index, that becomes the table. All the data is stored at the leaf level of the index. So when you have a clustered index, and you get a scan, you'll only ever see a clustered index scan and never a table scan. This is because the cluster is the table.
3)
Why to have Full CL_IX Scan if Full Table Scan is smaller in size (and seems to me, less complicted)?
I feel I miss key points how/why/when it is used and what it really constitutes.
AQny explanations are welcome.
I asked already similar question in "Why/when/how is whole clustered index scan chosen rather than full table scan?" but I cannot further clear there my doubts since stackoverflow.com is not discussion (but question and answer board) and I already had been banned there over a dozen of times for starting "discussions" there (well, in ServerFault.com).
The thing is, I wouldn't "choose" to have a clustered index scan over a table scan. This is because, they are effectively the same thing. In general, there are exceptions, a scan, table or index, is a non-desirable result. Is there some overhead in a cluster scan as opposed to a heap scan? Yeah, probably, but there are a TON of benefits if that index is getting used appropriately that far outweigh any cost associated when the index is used incorrectly. However, it does point out the importance of finding just the right column or columns for the clustered index. If it's not getting used, very frequently, for data access, you have a problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply