> it is isn't atomic
merge is a single, logically-atomic statement in sql server (with or without hints)
it doesn't have the same physical locking characteristics as a single insert or update statement, how could it?
merge is a single, logically-atomic statement in sql server (with or without hints)
it doesn't have the same physical locking characteristics as a single insert or update statement, how could it?
It really feels like it was a half-assed feature.
Great that they followed the standard, not so great that they made it nearly unusable with such a poor implementation.
I was super excited when we got SQL server 2008 at work (since upserts are a relatively common issue) and was devastated to learn all the issues with MERGE. I didn't care that the syntax was funky, I just wanted to avoid needing to manage transactions/locks/etc.
[0] https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-cond...