• I should have been clearer - I would recommend creating BOTH indexes in either of the two sets, not just one or the other. Because the query has two outer joins, one on HID+Start and one on HID+Finish, I would create the pair of indexes (in either style - although the first ones with the composite keys are probably always going to be better in this case).

    (edited)

    BTW - the (possibly?) optimum configuration is probably:

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

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

    This will organize the data by HID+Start as well as creating efficient indexes.