It looks to me like the following is occurring.

Where there is no Unique index (or constraint) on the HID column (and/or the statistics don't show that the HID column is a nice flat histogram), the query optimizer decides to do a table scan on the table, which appears to be pretty efficient. (i.e., read a row, compare all three fields and update in one pass.) The table shown is pretty compact, and without knowing the probable percentage of rows to be updated in advance, a table scan may be the best solution.

However, when the Uniqueness constraint is defined, or the statistics show the flat distribution of HID, the optimizer decides that the index is worth using. Unfortunately, this causes lots of index seeks, followed by row fetches of a lot of rows in order to compare the datetime columns (it may even wind up doing two data row fetches per actual data row as a result of the two separate left joins on the different datetime columns.

I would bet that either of the following would perform very well:

create unique index IDX_Hidvals_hid_start on #hidvals(hid,start);

create unique index IDX_Hidvals_hid_finish on #hidvals(hid,finish);

or

create unique index IDX_Hidvals_hid_start on #hidvals(hid) include (start);

create unique index IDX_Hidvals_hid_finish on #hidvals(hid) include (finish);

Either of these would eliminate the need to do the data row fetches to validate the "start" or "finish" criteria, and should be at least as fast as the non-unique clustered index (probably a lot faster - assuming that the update isn't updating a large percentage of the table rows).

Clustering one of the two indexes might or might not make a difference - what is ordered well by HID+Start may not be ordered well for a HID+Finish query, although if the dates "tend" to be similarly ordered, then I would cluster the HID+Start index.