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

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

Oorspronkelijke productversie: SQL Server
Oorspronkelijk 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 ROW_NUMBER-functie die werd 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 moeten worden verwijderd en welke records moeten worden verwijderd op basis van de sorteervolgorde van andere kolommen, kunt u de ORDER BY expressie gebruiken om dit te doen.

Meer informatie

Methode 2 is om deze redenen eenvoudig en effectief:

• U hoeft de dubbele records niet tijdelijk naar een andere tabel te kopiëren.
• U hoeft de oorspronkelijke tabel niet met zichzelf samen te voegen (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 hebben in de tabel die gebruikmaakt van de key_value indexsleutel en eventuele sorteerkolommen bevat die u mogelijk in de ORDER BY expressie hebt gebruikt.

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