An inline function is a good idea, but I'll leave it scalar for now so you can just replace your existing function.  I would definitely not use I/O to do...

Cluster the header table on the date column.
Cluster the detail table on the key from the header table.  Get the key/fk by querying the header table first.
Edit: On the...

1) Technically, no, since clus key column(s) are automatically included in all non-clus indexes. But I like to explicitly code it when it's needed to make this index a...

Presumably the lower number is always first for single entries (if not, fix that! 🙂 ).


SELECT L.*
FROM Links L
WHERE L.ClientRef < L.LinkedClientRef

Agree. something like below.  Keep the trigger code as efficient as possible.  (Btw, cluster the new table on LOG_TIME first, to make processing and clean up of that table easier.)

Embedded "magical" values are a terrible idea.  It violates 1st normal form, because you're trying to store two different pieces of info in one column.  That inevitably leads to query...