I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation.
First, take a look at this question I asked on Stack Overflow last week: Why does inserting a row with a foreign key referencing a row by pk modified in another snapshot isolation transaction cause the transaction to hang?
In our database we have 4 tables. Two has a foreign key to One's primary clustered key. Four has a foreign key to Three via a surrogate key. In all cases, the keys are meaningless.
Imagine two scenarios:
In a snapshot transaction where a column is modified in One, another snapshot transaction begins where a row is simply inserted into Two, where the foreign key column has the value of the row being modified in One. The latter transaction will block until the former transaction is committed, and then the latter transaction will abort with a snapshot isolation error.
In a very similar situation, a snapshot transaction begins where a column is modified in Three, another snapshot transaction begins where a row is simply inserted into Four, where the foreign key column has the value of the row being modified in Three (via its surrogate key). The latter transaction will finish without issue before the former transaction is committed.
Despite the answer being rather simple, this is unintuitive to me. I almost want to consider this a bug in SQL Server. The first scenario's transaction doesn't have a legitimate reason to block, and it especially doesn't have a legitimate reason to roll back the transaction. It does so basically because of an implementation detail. But if you just use a surrogate foreign key, everything works as expected and nothing is blocked or conflicts.
Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?