• Maybe I didn't get something but I have not heard anything about the rule: "for each unique value of the natural key there should be only one value of the surrogate key". I always thought that SCD works differently. So my questions are:

    1. Where is a Primary Key on the table? This is a fundamental requirement for ANY relational table.

    2. How are you going to join a Fact table to this dimension table if there is no unique constraint?

    3. Isn't it a best practise to create surrogate key as autoincremental IDENTITY field? So it can be a Primary Key as well and in this case why do we need any special unique constraints on an indexed view?

    For DW with SCD Type 2 I found useful this index to check Natural Key uniqueness:

    CREATE UNIQUE INDEX [IndexName] ON [TableName](Natural_Key) WHERE Date_To IS NULL


    Alex Suprun