• Personally I put the Primary Key clustered index on the surrogate Key in the Dimension, with a non-clustered index on the Business key.

    Unless the dimension is a Type 1, I would avoid putting a Unique index on the business key as with Type 2 dimensions you will get repeated business key, and therefore need to add an alternative column to maintain the uniqueness.

    The main reason is that Surrogate keys are the glue that bind Fact table to dimension table, and so if you want to enforce RI (which I would recomend) between Fact and Dimension then the Surrogate key needs to be the PK.

    As for clustering, the reason I suggest the Surrogate key as the clustered index is that you are only ever adding to the end of the Index rather than with a business key which you can be inserting data into the middle of the index thus leading to heavier fragmentation of said index.

    That doesnt mean I wouldnt create a Non-clustered index on the business key, which is be the sensible thing to do in order to aid the ETL and Filtering on the reporting side.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices