Unique Clustered Index on multiple fields

Last Post 26 Nov 2011 06:58 PM by rm. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
timjohnstone
New Member
New Member

--
25 Nov 2011 06:15 AM
I have a table where the Clustered Index is unique and made up of 5 fields as follows:

ProductID int, ProductSCDID int, StartDateID int, StoreID smallint, SellingPriceTypeCode int

In a table with 550 million rows, this is quite a large clustered index.

Would it be more efficient for storage and index creation if I created a computed column which creates a unique hash value of all these keys, and make that the unique clustered index?

The queries make better use of the non-clustered indexes than they do this clustered index, so I'm not concerned about how useful it is for querying.
Also, the table is truncated daily and re-loaded from a warehouse so the clustered index is dropped and re-created every time. I'm trying to make the creation of the clustered index as fast as possible.

I'm having the debate about whether the table needs a clustered index at all seperately. This is just to decide whether there's any benefit in investigating this as a possible solution. Has anyone tried it before?

Regards
Tim
rm
New Member
New Member

--
25 Nov 2011 06:54 AM
Depends on how you load and query data, may don't need clustered index at all.
timjohnstone
New Member
New Member

--
25 Nov 2011 07:46 AM
It would appear as if the developer used the clustered index purely to enforce a primary key constraint on this mix of fields.
I think it'd be better to make it a Primary Key constraint, but have no Clustered Index.

What I need to do is build a list of potentially worthwhile scenarios and test them all. Creating a computed column as a clustered index is perhaps the most complicated to implement so I'm trying to cut out the bad ideas and stick to the good ones.

Just wondering whether this computed column idea is a good one or a bad one from the point of view of creating and storing the index.
rm
New Member
New Member

--
26 Nov 2011 06:58 PM
If really like to have clustered index, I'll use identity column. It costs less than computed column. By the way, pkey enforces uniqueness even it's non-clustered. Not sure why need computed column here.


Acceptable Use Policy
---