Alexander , you are right : in my script is missing the Primary Key definition for the dimension .
The primary key will be on Surrogate_Key, Date_From .
This is not orthodox implementation of SCD Type 2 : the reason-to-be for Surrogate_Key is not to be and identity,
but to replace the alphanumeric Natural_Key with a numeric sequence to perform more efficient joins and
to take less space in fact tables.
Fact tables should contain Surrogate Key and Fact_Date to be joined with the dimension
select * from fact_table a left join dim_scd_prova b on a.Surrogate_Key = b.Surrogate_Key
and a.Event_Date between b.Date_From and isnull(b.date_to, '2100-01-01')
This method , although not orthodox, works fine, also because in general all fact tables contains a date.
Not using the Surrogate_Key would require to perform less efficient joins on the Natural_Key,
that is in general alphanumeric
This method requires too that for each Natural_Key there is only one Surrogate_Key, and my solution guarantees it
at the database level .
Your proposed solution
CREATE UNIQUE INDEX [IndexName] ON [TableName](Natural_Key) WHERE Date_To IS NULL
is clever, but does not examine the whole table, but only the current version,
and in some cases is not enough