>If you do not have an index on Order.UserId column, then the deletion of the User record will be slow when the Order table is large. not only slow per.se. The

>If you do not have an index on Order.UserId column, then the deletion of the User record will be slow when the Order table is large. not only slow per.se. The full table scan in this case would also usually take the lock on the whole Order table instead of only narrow block/range-level lock in the index like it would in the case of indexed FK. Such table lock may force some other threads/sessions to wait, and provides increased surface for deadlocks between seemingly unrelated transactions.