Dubbele rijen uit een SQL Server tabel verwijderen met behulp van een script

  • Artikel

Dit artikel bevat een script dat u kunt gebruiken om dubbele rijen uit een tabel in Microsoft SQL Server te verwijderen.

Originele productversie: SQL Server
Origineel KB-nummer: 70956

Samenvatting

Er zijn twee algemene methoden die u kunt gebruiken om dubbele records uit een SQL Server-tabel te verwijderen. Begin voor demonstratie met het maken van een voorbeeldtabel en gegevens:

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

Probeer vervolgens de volgende methoden om de dubbele rijen uit de tabel te verwijderen.

Methode 1

Voer het volgende script uit:

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

Dit script voert de volgende acties uit in de gegeven volgorde:

  • Verplaatst één exemplaar van een dubbele rij in de oorspronkelijke tabel naar een dubbele tabel.
  • Hiermee verwijdert u alle rijen uit de oorspronkelijke tabel die zich ook in de dubbele tabel bevinden.
  • Hiermee verplaatst u de rijen in de dubbele tabel terug naar de oorspronkelijke tabel.
  • De dubbele tabel wordt verwijderd.

Deze methode is eenvoudig. U moet echter voldoende ruimte in de database hebben om de dubbele tabel tijdelijk te bouwen. Deze methode brengt ook overhead met zich mee omdat u de gegevens verplaatst.

Als uw tabel een kolom IDENTITY heeft, moet u ook SET IDENTITY_INSERT ON gebruiken wanneer u de gegevens terugzet naar de oorspronkelijke tabel.

Methode 2

De functie ROW_NUMBER die is geïntroduceerd in Microsoft SQL Server 2005 maakt deze bewerking veel eenvoudiger:

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1

Dit script voert de volgende acties uit in de gegeven volgorde:

  • Gebruikt de functie ROW_NUMBER om de gegevens te partitioneren op basis van de key_value die een of meer kolommen kunnen zijn, gescheiden door komma's.
  • Verwijdert alle records die een DupRank waarde hebben gekregen die groter is dan 1. Deze waarde geeft aan dat de records duplicaten zijn.

Vanwege de (SELECT NULL) expressie sorteert het script de gepartitioneerde gegevens niet op basis van een voorwaarde. Als voor uw logica voor het verwijderen van duplicaten moet worden gekozen welke records u wilt verwijderen en welke u wilt bewaren op basis van de sorteervolgorde van andere kolommen, kunt u de ORDER BY expressie hiervoor gebruiken.

Meer informatie

Methode 2 is om de volgende redenen eenvoudig en effectief:

  • U hoeft de dubbele records niet tijdelijk naar een andere tabel te kopiëren.
  • Hiervoor hoeft u de oorspronkelijke tabel niet samen te voegen met zichzelf (bijvoorbeeld door een subquery te gebruiken die alle dubbele records retourneert met behulp van een combinatie van GROUP BY en HAVING).
  • Voor de beste prestaties moet u een bijbehorende index in de tabel hebben die de key_value als indexsleutel gebruikt en alle sorteerkolommen bevat die u mogelijk in de ORDER BY expressie hebt gebruikt.

Deze methode werkt echter niet in verouderde versies van SQL Server die de functie ROW_NUMBER niet ondersteunen. In deze situatie moet u in plaats daarvan methode 1 of een vergelijkbare methode gebruiken.