Entfernen doppelter Zeilen aus einer SQL Server-Tabelle mithilfe eines Skripts

Dieser Artikel enthält ein Skript, mit dem Sie doppelte Zeilen aus einer Tabelle in Microsoft SQL Server entfernen können.

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 70956

Zusammenfassung

Es gibt zwei gängige Methoden, mit denen Sie doppelte Datensätze aus einer SQL Server Tabelle löschen können. Zur Demonstration erstellen Sie zunächst eine Beispieltabelle und Daten:

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)

Versuchen Sie dann die folgenden Methoden, um die doppelten Zeilen aus der Tabelle zu entfernen.

Methode 1

Führen Sie folgendes Skript aus:

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

Befolgen Sie anschließend die folgenden Schritte in der vorgegebenen Reihenfolge:

• Verschiebt eine Instanz einer beliebigen doppelten Zeile in der ursprünglichen Tabelle in eine durch Verdoppelung entstandene Tabelle.
• Löscht alle Zeilen aus der ursprünglichen Tabelle, die sich auch in der doppelten Tabelle befinden.
• Verschiebt die Zeilen in der doppelten Tabelle wieder in die ursprüngliche Tabelle.
• Lässt die durch Verdoppelung entstandene Tabelle fallen.

Diese Methode ist einfach. Es erfordert jedoch, dass In der Datenbank genügend Speicherplatz zur Verfügung steht, um vorübergehend die verdoppelte Tabelle zu erstellen. Diese Methode verursacht auch einen Mehraufwand, da Sie die Daten verschieben.

Wenn Ihre Tabelle eine IDENTITY-Spalte aufweist, müssen Sie außerdem SET IDENTITY_INSERT ON verwenden, wenn Sie die Daten in der ursprünglichen Tabelle wiederherstellen.

Methode 2

Die in Microsoft SQL Server 2005 eingeführte funktion ROW_NUMBER vereinfacht diesen Vorgang erheblich:

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

Befolgen Sie anschließend die folgenden Schritte in der vorgegebenen Reihenfolge:

• Verwendet die ROW_NUMBER Funktion, um die Daten basierend auf den key_value zu partitionieren. Dies können eine oder mehrere Spalten sein, die durch Kommas getrennt sind.
• Löscht alle Datensätze, die einen DupRank Wert erhalten haben, der größer als 1 ist. Dieser Wert zeigt an, dass die Datensätze Duplikate sind.

Aufgrund des (SELECT NULL) Ausdrucks sortiert das Skript die partitionierten Daten nicht basierend auf einer Bedingung. Wenn ihre Logik zum Löschen von Duplikaten erfordert, basierend auf der Sortierreihenfolge anderer Spalten auszuwählen, welche Datensätze gelöscht und beibehalten werden sollen, können Sie dazu den ORDER BY Ausdruck verwenden.

Weitere Informationen

Methode 2 ist aus folgenden Gründen einfach und effektiv:

• Es ist nicht erforderlich, dass Sie die doppelten Datensätze vorübergehend in eine andere Tabelle kopieren.
• Es ist nicht erforderlich, dass Sie die ursprüngliche Tabelle mit sich selbst verbinden (z. B. mithilfe einer Unterabfrage, die alle doppelten Datensätze mit einer Kombination aus GROUP BY und HAVINGzurückgibt).
• Um eine optimale Leistung zu erzielen, sollten Sie über einen entsprechenden Index für die Tabelle verfügen, die als key_value Indexschlüssel verwendet wird und alle Sortierspalten enthält, die ORDER BY Sie möglicherweise im Ausdruck verwendet haben.

Diese Methode funktioniert jedoch nicht in veralteten Versionen von SQL Server, die die ROW_NUMBER-Funktion nicht unterstützen. In diesem Fall sollten Sie stattdessen Methode 1 oder eine ähnliche Methode verwenden.