Quote:
quote:Originally posted by mehdi62b
Jeff,I have a question?
if we change the clustered index of primary column(to nonclustered index)so we
could make this column (TD_Exp_Status) as clustered,
whether query processor considers our column to be sorted
correctly regardless of other alternatives?
--------------------------------------------
Mehdi.:)
|
I'm not sure I can correctly parse your question.
The query processor is aware of the fact that the clustered index orders the data rows. This is why a clustered index is nice to use for range queries: the processor knows that the data is in order by the index column and can simply scan rows sequentially for the range it is searching for, and then, the rest of the data is just "there".
The processor also knows that indexes (clustered or not) order the data according to the index key and will attempt to use this information when trying to satisfy an ORDER BY clause. Just remember that other operations (like JOINs) may mean this is not possible and a sort will have to be done regardless of the indexes available.
A requirement of the clustered index is that each row can be uniquely identified. This is because other indexes contain the clustered index key and use that to obtain the data row. So, if you use a column which is not unique, the system will add a hidden "unique-ifier" to the clustered index key. This extra overhead is carried along in all the other indexes on the table.
Because of this, I don't think it is a good idea to use a non-unique key as the clustered index key. I also don't think any benefit is obtained by placing an index on a column value which has a small range of distinct values. If the index is on a column is not very specific, the query processor is aware of this, and may very well decide the index isn't worth the bother, since it will have to scan all the duplicates the index entry refers to.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
