Remove Duplicate Rows Without Losing the Wrong Record

The difficult part of deduplication is not writing DELETE. It is deciding which rows represent the same business entity, which information must survive, and how references will remain valid. Two rows with the same email are not necessarily the same customer, and the highest identity value is not automatically the most trustworthy record.

Define sameness and the survivor

Write the duplicate key as an explicit business rule. Include tenant or source-system boundaries when appropriate. Specify whether case, accents, whitespace, and missing values matter. SQL Server comparisons follow collation rules, and grouping NULL values together may be inappropriate when NULL means an unknown identity rather than one shared identity.

Choose a deterministic survivor rule. In the practice data, the business key is TenantId plus ExternalKey, and the most recently observed row wins. RowId breaks timestamp ties. That final unique tie-breaker matters: without it, two equal timestamps can yield different survivors across executions.

The sample intentionally excludes missing ExternalKey values from deduplication. Each unknown record remains independent until a separate reconciliation process can identify it. That is one possible policy, not a universal interpretation of NULL.

CREATE TABLE #CustomerStage
(RowId int PRIMARY KEY, TenantId int NOT NULL,
 ExternalKey varchar(20) NULL, SeenAt datetime2(0) NOT NULL);
INSERT #CustomerStage VALUES
(1,1,'A','2020-01-01'),(2,1,'A','2020-02-01'),
(3,2,'A','2020-01-01'),(4,1,NULL,'2020-01-01'),
(5,1,NULL,'2020-02-01');

;WITH ranked AS
( SELECT *, ROW_NUMBER() OVER
  (PARTITION BY TenantId, ExternalKey
   ORDER BY SeenAt DESC, RowId DESC) AS rn
  FROM #CustomerStage WHERE ExternalKey IS NOT NULL )
SELECT * FROM ranked WHERE rn > 1 ORDER BY RowId;

The expected victim is RowId 1. RowId 2 is newer for tenant 1 and key A. RowId 3 belongs to another tenant, and rows 4 and 5 have unknown external identity. Explain those outcomes to the business owner before turning the preview into a write.

Separate selection from controlled execution

The deletion below is safe to experiment with because it modifies only the temporary fixture and rolls back. OUTPUT captures exactly the removed rows. After rollback, the original table remains intact. For a production cleanup, replace the fixture only after validating the rule, impact count, recovery plan, and write-concurrency strategy.

BEGIN TRAN;
;WITH ranked AS
( SELECT *, ROW_NUMBER() OVER
  (PARTITION BY TenantId, ExternalKey
   ORDER BY SeenAt DESC, RowId DESC) AS rn
  FROM #CustomerStage WHERE ExternalKey IS NOT NULL )
DELETE FROM ranked
OUTPUT deleted.RowId, deleted.TenantId, deleted.ExternalKey
WHERE rn > 1;
ROLLBACK;

A preview executed hours earlier is not a guarantee about the later DELETE. New rows can arrive, timestamps can change, and the desired survivor can move. Use a controlled maintenance window or a deliberate isolation strategy covering selection, reference migration, deletion, and uniqueness enforcement. Large serializable transactions can impose substantial blocking, so choose the procedure from operational constraints rather than assuming stronger isolation is free.

Before deleting parent rows, inventory referencing foreign keys and relationships not enforced by foreign keys. If child rows reference duplicate identities, create a victim-to-survivor mapping and redirect children according to business rules. Two children may then collide on their own unique key. Resolve that collision explicitly; disabling constraints merely hides it.

Preserve meaning and prevent recurrence

The newest row may have an empty address while the older row has a verified address. If valuable attributes are distributed across duplicates, simple survivor selection loses information. Merge fields under documented precedence rules first, keeping provenance where necessary. The resulting survivor may be a consolidated record rather than an unchanged original row.

Retain an auditable mapping and sufficient removed data for the agreed recovery period. A database backup is essential but may be an inconvenient way to restore a handful of relationships after subsequent legitimate writes. A targeted recovery record should identify both original keys and the transformation performed, with appropriate access controls for personal data.

After cleanup, enforce the actual uniqueness rule where it belongs. A suitable unique constraint or filtered unique index can prevent recurrence, but must match the chosen treatment of unknown keys. Fix the ingestion race or missing idempotency check that created duplicates as well; repeated cleanup is not a durable ingestion design.

Validate counts by tenant and business key, surviving attribute values, child references, and a retry of the previously problematic import. A successful cleanup removes only confirmed duplicates, keeps valuable information, and establishes a mechanism that rejects the next duplicate instead of waiting for another deletion job.

Technical references: Microsoft Learn: ROW_NUMBER · Microsoft Learn: Unique indexes.