Friday, February 24, 2012

covering indexes

Hi,
I asked the similar question before but I have again some doubts about covering indexes.
Besides, tomorrow I have a Microsoft MCAD 70-229 exam so please help me.
In here,
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate

we create composite nonclustered index for both OrderID and OrderDate column.
Leftmost is OrderID.
So

when our first research is happening(Where clause), the only

nonclustered index which is used is OrderID index. And then, when we

pass through the second search(ORDER BY clause), OrderDate index become activated.
So

we can say that the seleection of indexes in composite indexes is

determined according to the situation of the query at that time.

Hence, is this all correct ?

Best wishes,
Mert

Hmm,
Now thanks to the strict studies on indexes, I conceived that my approach to this is completely false.
So again in my opinion, the best solution for the above query is that we must use two separate indexes and for Ordeing we should use nonclustered index since it brings much performance benefit, for instance; we do not have to look up the data table since we will have a available ordered set.
So would you please explain your own opinions ?

Thanks

No comments:

Post a Comment