Deduplication

Keep a single row per business key: rank within each group, then delete.

≈ 2 min readReading

On this page

Problem statement
Data set
Procedural versus set-based
1. Procedural
2. Set-based
Expected result
Why set-based is preferable
Pitfalls
Variants
Verifying equivalence
SQL Server versions

Problem statement#

In dbo.Contacts, keep only one contact per email address: the most recent one; on equal dates, the one with the highest identifier.

Data set#

CREATE TABLE dbo.Contacts
(
    ContactId    int           NOT NULL PRIMARY KEY,
    Email        nvarchar(320) NOT NULL,
    DateCreation datetime2(0)  NOT NULL
);

INSERT INTO dbo.Contacts (ContactId, Email, DateCreation)
VALUES (1, N'a@exemple.org', '2026-01-01'),
       (2, N'a@exemple.org', '2026-02-01'),
       (3, N'b@exemple.org', '2026-01-15'),
       (4, N'a@exemple.org', '2026-02-01');   -- same date as contact 2

Procedural versus set-based#

Procedural#

DECLARE @Email nvarchar(320);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT Email FROM dbo.Contacts GROUP BY Email HAVING COUNT(*) > 1;
OPEN c;
FETCH NEXT FROM c INTO @Email;
WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE FROM dbo.Contacts
    WHERE  Email = @Email
      AND  ContactId <> (SELECT TOP (1) ContactId
                         FROM   dbo.Contacts
                         WHERE  Email = @Email
                         ORDER BY DateCreation DESC, ContactId DESC);
    FETCH NEXT FROM c INTO @Email;
END;
CLOSE c;
DEALLOCATE c;

Set-based#

WITH D AS
(
    SELECT ContactId, Email, DateCreation,
           ROW_NUMBER() OVER (PARTITION BY Email
                              ORDER BY DateCreation DESC, ContactId DESC) AS Rang
    FROM   dbo.Contacts
)
DELETE FROM D
WHERE  Rang > 1;

Expected result#

Contacts 3 and 4 remain: for a@exemple.org, the most recent one is kept, and the date tie is broken by the highest identifier (contact 4).

Why set-based is preferable#

ROW_NUMBER assigns a rank to each row within its group in a single pass; deleting the ranks greater than 1 removes all duplicates in one statement.
The ordering DateCreation DESC, ContactId DESC is unique: the result is deterministic, even with ties.
The procedural version relaunches a lookup and a delete for each duplicated address.

Pitfalls#

Without a tie-breaking key, the order among ties is arbitrary: two executions may keep different rows.
Definition of a duplicate: the comparison follows the column's collation (case-insensitive by default in most cases). If the addresses contain superfluous spaces, normalize in the PARTITION BY (LOWER(LTRIM(RTRIM(Email)))).
Deleting is irreversible: start with a SELECT of the rows with rank > 1, archive with OUTPUT, work inside a transaction.
Prevent duplicates from coming back with a constraint: CREATE UNIQUE INDEX UX_Contacts_Email ON dbo.Contacts (Email);

Variants#

-- 1) Diagnosis before deletion
WITH D AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY DateCreation DESC, ContactId DESC) AS Rang FROM dbo.Contacts)
SELECT * FROM D WHERE Rang > 1;

-- 2) Deletion with archiving of the deleted rows
WITH D AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY DateCreation DESC, ContactId DESC) AS Rang FROM dbo.Contacts)
DELETE FROM D
OUTPUT deleted.ContactId, deleted.Email, deleted.DateCreation
INTO   dbo.Contacts_Archive (ContactId, Email, DateCreation)
WHERE  Rang > 1;

Verifying equivalence#

A change of method is only worthwhile if the result is identical. Compare in both directions; no rows should come out.

-- No more duplicates, and exactly one contact per address
SELECT Email, COUNT(*) AS Nb FROM dbo.Contacts GROUP BY Email HAVING COUNT(*) > 1;
-- Expected result: no rows.

SELECT ContactId FROM dbo.Contacts ORDER BY ContactId;
-- Expected result with the test data set: 3 and 4.

SQL Server versions#

ROW_NUMBER and deletion through a CTE: SQL Server 2005 and later.