Without WITH, OPENJSON hands you text. With it, you pick the names and types and decide what happens when a property is missing.
The Outbox Table: Publishing Changes Reliably From a Transaction
Orders commit and the message vanishes? Save both in one transaction, then let a worker claim, send and retry with a lease. Duplicates are still possible.
MERGE Statement Quiz: What If Two Source Rows Match One Target?
This MERGE Statement Quiz asks what happens when two source rows match one target row. The answer is error 8672, and the whole statement fails. A tested script shows the error, the fix with ROW_NUMBER and the same change written as UPDATE plus INSERT.
OUTPUT Clause Quiz: What Do inserted and deleted Hold?
This OUTPUT Clause Quiz raises a price from 10 to 12 and asks which two values come back. The answer is 10 and 12, because deleted holds the old row and inserted the new one. A tested script proves it and shows OUTPUT with INSERT, DELETE and an audit table.
Destructive Reads: Pulling Work From a Queue Table With OUTPUT
Claiming a job and finishing it are two different things. Try a delete claim, a rollback, and an update claim on a tiny queue.
Shredding sp_server_diagnostics Output Into Health Rows
Check shredding sp_server_diagnostics with SQL Server examples, practical queries, clear limits, and steps to verify your own database.
MERGE Statement Pitfalls
MERGE statement pitfalls include duplicate source keys, concurrency races, trigger surprises, and complex actions that deserve testing.