i

Description

Deux maisons en brique quasi identiques dans une rangée : des doublons presque parfaits.

Source

Smallbones · page d’origine

Licence

CC0 Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Dédoublonnage

Garder une seule ligne par clé métier : un classement par groupe, puis une suppression.

Sur cette page

1. Énoncé
2. Jeu de données
3. Procédural contre ensembliste
   1. Procédural
   2. Ensembliste
4. Résultat attendu
5. Pourquoi l'ensembliste est préférable
6. Pièges
7. Variantes
8. Vérifier l'équivalence
9. Versions de SQL Server

Énoncé#

Dans dbo.Contacts, conserver un seul contact par adresse e-mail : le plus récent ; à date égale, celui dont l'identifiant est le plus élevé.

Jeu de données#

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');   -- même date que le contact 2

Procédural contre ensembliste#

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;

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;

Résultat attendu#

Restent les contacts 3 et 4 : pour a@exemple.org, le plus récent est conservé, et l'égalité de date est départagée par l'identifiant le plus élevé (contact 4).

Pourquoi l'ensembliste est préférable#

• Le ROW_NUMBER attribue un rang à chaque ligne au sein de son groupe en un seul passage ; supprimer les rangs supérieurs à 1 supprime tous les doublons en une instruction.
• L'ordre DateCreation DESC, ContactId DESC est unique : le résultat est déterministe, même avec des ex æquo.
• La version procédurale relance, pour chaque adresse en double, une recherche et une suppression.

Pièges#

• Sans clé de départage, l'ordre entre ex æquo est arbitraire : deux exécutions peuvent conserver des lignes différentes.
• Définition du doublon : la comparaison suit la collation de la colonne (insensible à la casse par défaut dans la plupart des cas). Si les adresses contiennent des espaces superflus, normaliser dans le PARTITION BY (LOWER(LTRIM(RTRIM(Email)))).
• Supprimer, c'est irréversible : commencer par un SELECT des lignes de rang > 1, archiver avec OUTPUT, travailler dans une transaction.
• Empêcher le retour des doublons par une contrainte : CREATE UNIQUE INDEX UX_Contacts_Email ON dbo.Contacts (Email);

Variantes#

-- 1) Diagnostic avant suppression
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) Suppression avec archivage des lignes supprimées
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;

Vérifier l'équivalence#

Un changement de méthode ne vaut que si le résultat est identique. Comparer dans les deux sens ; aucune ligne ne doit sortir.

-- Plus aucun doublon, et exactement un contact par adresse
SELECT Email, COUNT(*) AS Nb FROM dbo.Contacts GROUP BY Email HAVING COUNT(*) > 1;
-- Résultat attendu : aucune ligne.

SELECT ContactId FROM dbo.Contacts ORDER BY ContactId;
-- Résultat attendu avec le jeu d'essai : 3 et 4.

Versions de SQL Server#

ROW_NUMBER et suppression à travers une CTE : SQL Server 2005 et ultérieur.