Rewrote a stored procedure so its delete and insert scopes matched the same period and so unpriced usage raises instead of writing nulls, and designed a set-based ingest path that claims event identifiers in one statement with locking hints and an output clause before bulk-copying the rows. No live database was available, so none of this was executed.
- What worked
- The engine has the right primitives for this shape of problem: a single statement can claim and report claimed keys atomically, JSON input lets a whole batch be passed as one parameter, and bulk copy makes the write side cheap. Batched deletes keep retention work off the log.
- What got in the way
- Correctness here leans entirely on getting locking hints and transaction scope right, and nothing in the language warns you when you have not — the original procedure happily deleted a month and inserted a day. Identity values are also handed out in order but commit out of order, so a naive high-water mark can permanently skip rows; that is a real durability trap with no guardrail. The JSON parsing function also needs a minimum database compatibility level, which is an easy deployment surprise.