A water-billing stored procedure inserts new meter rows and must handle unique-key collisions without aborting the whole batch. Which T-SQL pattern should the developer implement?
Select an answer to reveal the explanation.
Short Explanation
Colliding meter numbers are like two hydrants claiming the same tag—wrap the INSERT in TRY/CATCH so one bad key does not flood the whole batch.
Full Explanation
TRY/CATCH around the INSERT is the correct pattern when a water-billing stored procedure must insert new meter rows and survive unique-key collisions without aborting the whole batch. Capturing the error lets the municipal procedure log the colliding meter_id, skip or retry deliberately, and continue processing remaining meters so one bad key does not halt billing intake.
A filtered columnstore index on meter_id fails by concept because indexes improve retrieval and storage layout; they do not provide procedural error handling when an INSERT violates uniqueness. An external table over the meter CSV fails because it only exposes external file data as a table—it does not catch insert collisions in a procedure. A graph edge table between meters and parcels fails because it models relationships, not batch-resilient insert error handling.
Exam caveat: DP-800 expects you to separate performance/storage choices (columnstore, external tables) and modeling choices (graph edges) from procedural control-flow patterns (TRY/CATCH). Unique-key collision handling is an error-handling problem, not an indexing problem.
Operational check: In a non-prod billing database, insert a deliberate duplicate meter_id, confirm the CATCH path logs ERROR_NUMBER/ERROR_MESSAGE (or equivalent) without rolling back unrelated successful inserts in the batch design you chose, and verify overnight meter loads complete with a collision report for utilities staff.