MERGE vs SELECT/INSERT: Choosing an Idempotent Update Strategy Under Azure SQL Retry Policies

When a transient error (e.g., 4060, 40197) interrupts a write, Azure SQL’s retry policy can re‑issue the command. The goal is to guarantee that a retry does not produce duplicate rows.

Azure’s retry policy applies to individual SQL statements but not automatically to multi‑statement stored procedures, which can lead to partial re‑execution and unintended duplicates if the logic is not idempotent.

Two documented patterns exist: a single MERGE statement that updates or inserts based on a unique key, and a SELECT‑then‑INSERT sequence guarded by a transaction and optimistic concurrency. Each has trade‑offs around concurrency, trigger behavior, and transaction size.

Will MERGE reliably avoid duplicates when retried after a transient error in the presence of triggers or cascading foreign keys? Does the SELECT/INSERT pattern provide stronger idempotency guarantees when the entire stored procedure is retried? Under Azure SQL’s retry policy, which pattern better handles partial re‑execution of multi‑statement procedures without duplicating writes?