Are clustered index columns actually usable in non-clustered indexes?
-
I have read that non-clustered indexes are built using the clustered index keys. Does that mean that explicitly including columns present in a clustered index in the definition of a non-clustered index is redundant? Suppose I have a clustered index on TransactionDate, and want to query by (WorkOrder, TransactionDate) Should I declare the non-clustered index to have both (WorkOrder, TransactionDate), or is just (WorkOrder) sufficient to cover the query?
-
Just WorkOrder, you will also find that if the only value to return is TransactionDate based on choice of looking up a workorder that the index still acts as a covering index in that situation because both values are present in the non-clustered index.
-
IF you already have the clustered index WorkOrder, TransactionDate DO NOT add a NON-CLUSTER index to do the same thing. This is redundant and could cause perf. issues
Good Hunting!AJ Ahrens
webmaster@kritter.net -
That's why you 'll want to have your clustering keys as small as possible.
All your NCI's will be affected because they nolonger contain RIDs but CI-keys.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply