jcrawf02 (11/18/2009)
Adding a clustered index that covers multiple columns within a table is not necessarily advantageous, because it will expand all of the non-clustered indexes as well. (from part II)
Yes
Therefore, the clustered index should be as narrow as possible, and the non-clustered index should be created as a composite key to assist with querying?
Yes.
So if I have a heap that only has a unique value by combining three separate columns, I should create a new column with a unique value (using newsequentialid() or some such) and make that the clustered index, but use the composite non-clustered index to help my queries against the table?
Maybe.
Remember that clustered index != primary key. While it is a good idea to put the cluster on a unique column, it's not obligatory. SQL can make the clustered index unique (by adding an int behind the scenes) if the column it's defined on is not. Look at what columns exist. Are any of them fairly good candidates for the cluster? Are any reasonably good?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability