Lynn Pettis (1/7/2013)
Your code still only runs the test code for one of the indexes not both. If I want the execution plans for the queries using the clustered index now I still have to copy your code around to do it.Both queries using the nonclustered index use the index (although the second query says an index on ColA, ColC would help). The second has to read more data from the index before going to the heap for further data which is why it probably runs a bit slower than the first.
Sorry that I am not able to tell my question more clearly,
I am not asking the difference between two types of indexes,
but my question is how with a A,B,C index an index is processed if only A and C are used in the Where clause.
Is the selection of the C done in the index tree?
Or is the selection of the C done after the pages have been read in to memory as a filter?
The example was given as a starting point, because often code is asked. The question is independend of the code, but the code is used to clearify (not succeeding) the question and providing a starting point.
It is possible that because of the B field missing in the selection the C field is not used while reading the rows into memory. And that the wrong C rows are filtered out as an 'afterthought'.
It is also possible that at a specific moment while using the index the engine can exclude rows on that they do not fit the C.
Both this may also be dependend on the size of the table or de index. The example produces over 100 000 rows (easely changed), but results might differ for differenc sizes of rows and/or tables.
Hope this does make sense,
Ben Brugman