A branch's handheld scanner occasionally uploads the same ticket-scan event twice after a connectivity drop, and a data engineer needs to remove these exact-duplicate rows from a Warehouse table using T-SQL rather than PySpark. Which T-SQL pattern correctly identifies and removes the duplicates while keeping exactly one copy of each?
Select an answer to reveal the explanation.
Short Explanation
Think of ROW_NUMBER() like numbering every copy of the same duplicated flyer as they come off the printer — copy 1, copy 2, copy 3 — and then only keeping copy 1. That per-group numbering is what lets T-SQL tell which duplicate to delete and which to keep.
Full Explanation
ROW_NUMBER() OVER (PARTITION BY <natural key> ORDER BY <tiebreaker>) assigns a sequential number to each row within a group of matching key values, and deleting every row where that number is greater than 1 leaves exactly one survivor per duplicate group — a standard, reliable T-SQL deduplication pattern. Adding DISTINCT to every downstream query hides the duplicates from that one query's output but leaves the underlying table just as duplicated, meaning every other consumer of the table still sees the bad data and every new query has to remember to add DISTINCT itself. CREATE STATISTICS only helps the query optimizer make better execution-plan decisions; it has no effect on the actual row data and doesn't identify or remove anything. Adding a UNIQUE constraint after duplicates already exist will simply fail, since the constraint can't be created against data that already violates it — uniqueness has to be enforced going forward, or the existing duplicates have to be cleaned up first. Before running the delete in production, run the ROW_NUMBER() query as a plain SELECT first to review exactly which rows would be removed.