Wie entferne ich doppelte Zeilen aus einer SQL Server-Tabelle?

Beim Entwerfen von Objekten in SQL Server müssen wir bestimmte Best Practices befolgen. Beispielsweise sollte eine Tabelle Primärschlüssel, Identitätsspalten, gruppierte und nicht gruppierte Indizes, Datenintegrität und Leistungsbeschränkungen enthalten. Die SQL Server-Tabelle sollte keine doppelten Zeilen gemäß den Best Practices im Datenbankdesign enthalten. Manchmal müssen wir uns jedoch mit Datenbanken befassen, in denen diese Regeln nicht befolgt werden oder in denen Ausnahmen möglich sind, wenn diese Regeln absichtlich umgangen werden. Obwohl wir die Best Practices befolgen, können Probleme wie doppelte Zeilen auftreten.

Beispielsweise könnten wir diese Art von Daten auch beim Importieren von Zwischentabellen abrufen und möchten redundante Zeilen löschen, bevor wir sie tatsächlich zu den Produktionstabellen hinzufügen. Darüber hinaus sollten wir die Aussicht auf das Duplizieren von Zeilen nicht verlassen, da doppelte Informationen die mehrfache Bearbeitung von Anforderungen, falsche Berichtsergebnisse und mehr ermöglichen. Wenn die Spalte jedoch bereits doppelte Zeilen enthält, müssen wir bestimmte Methoden befolgen, um die doppelten Daten zu bereinigen. Schauen wir uns in diesem Artikel einige Möglichkeiten an, um Datenverdopplungen zu entfernen.

Wie entferne ich doppelte Zeilen aus einer SQL Server-Tabelle?

In SQL Server gibt es eine Reihe von Möglichkeiten, doppelte Datensätze in einer Tabelle zu verarbeiten, basierend auf bestimmten Umständen wie:

Entfernen doppelter Zeilen aus einer eindeutigen Index-SQL Server-Tabelle

Sie können den Index verwenden, um die doppelten Daten in eindeutigen Indextabellen zu klassifizieren und dann die doppelten Datensätze zu löschen. Zuerst müssen wir Erstellen Sie eine Datenbank Mit dem Namen "test_database" erstellen Sie dann eine Tabelle "Employee" mit einem eindeutigen Index, indem Sie den unten angegebenen Code verwenden.

USE master GO CREATE DATABASE test_database GO USE [test_database] GO CREATE TABLE Mitarbeiter ([ID] INT NICHT NULL IDENTITY (1,1), [Dep_ID] INT, [Name] varchar (200), [email] varchar (250) NULL , [Stadt] varchar (250) NULL, [Adresse] varchar (500) NULL CONSTRAINT Primary_Key_ID PRIMARY KEY (ID))

Die Ausgabe erfolgt wie folgt.

Fügen Sie nun Daten in die Tabelle ein. Wir werden auch doppelte Zeilen einfügen. Die "Dep_ID" 003.005 und 006 sind doppelte Zeilen mit ähnlichen Daten in allen Feldern mit Ausnahme der Identitätsspalte mit einem eindeutigen Schlüsselindex. Führen Sie den unten angegebenen Code aus.

USE [test_database] GO INSERT INTO Employee (Dep_ID, Name, E-Mail, Stadt, Adresse) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro Or 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail. com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (003,' Aabharana, Sahni ',' [email protected] ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' [email protected] ',' OMAHA ',' 2975 Crouse Lane Burlington Nc 272150000 '), (005,' Aabram Howell ','[email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006 , 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7. St. Paul Mn 551063852'), (006, 'Humbaerto Acevedo', 'humbaerto.ac [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (007,' Pilar Ackaerman ',' [email protected] ',' ATLANTA ',' 5813 Eastern Ave. Hyattsville Md 207822201 '); SELECT * FROM Employee

Die Ausgabe wird wie folgt sein.

Ermitteln Sie nun die Anzahl der Zeilen in der Tabelle, indem Sie den folgenden Code ausführen. Die Funktion count (*) zählt keine Zeilen.

SELECT Dep_ID, Name, E-Mail, Stadt, Adresse, COUNT (*) AS duplicate_rows_count FROM Employee GROUP BY Dep_ID, Name, E-Mail, Stadt, Adresse

Die Ausgabe erfolgt wie folgt. Die im roten Feld hervorgehobenen Zeilen Nr. (3, 4), (6, 7), (8, 9) sind doppelt.

Unsere Aufgabe ist es, die Eindeutigkeit durch Entfernen von Duplikaten für die doppelten Spalten zu erzwingen. Es ist etwas einfacher, doppelte Werte mit einem eindeutigen Index aus der Tabelle zu entfernen, als die Zeilen ohne einen Index aus einer Tabelle zu entfernen. Im Folgenden sind zwei Methoden angegeben, um dies zu erreichen. Mit der ersten Methode können Sie mit der Funktion "row_number ()" doppelte Zeilen aus der Tabelle erstellen, während mit der zweiten Methode die Funktion "NOT IN" verwendet wird. Diese beiden Methoden haben ihre eigenen Kosten, auf die später eingegangen wird.

Methode 1: Auswählen doppelter Datensätze mit der Funktion „ROW_NUMBER ()“

Wählen Sie * aus (SELECT Dep_ID, Name, E-Mail, Stadt, Adresse, ROW_NUMBER () OVER (PARTITION BY Dep_ID, Name, E-Mail, Stadt, Adresse ORDER BY Dep_ID, Name, E-Mail, Stadt, Adresse) row_no FROM test_database.dbo.Employee ) x wobei row_no> 1 ist

Methode 2: Auswählen doppelter Datensätze mit der Funktion „NOT IN ()“

SELECT * FROM test_database.dbo.Employee WHERE ID NICHT IN (SELECT MAX (ID) FROM test_database.dbo.Employee GROUP BY Dep_ID, Name, E-Mail, Stadt, Adresse)

Führen Sie den obigen Code aus und Sie sehen die folgende Ausgabe. Beide Methoden führen zum gleichen Ergebnis, haben jedoch unterschiedliche Kosten.

Jetzt löschen wir die oben ausgewählten doppelten Zeilen mit "CTE" unter Verwendung des folgenden Codes. Der folgende Code wählt doppelte Zeilen aus, die mit der Funktion „ROW_NUMBER ()“ gelöscht werden sollen.

Methode 1: Löschen doppelter Datensätze mit der Funktion „ROW_NUMBER ()“

WITH cte_delete AS (SELECT Dep_ID, Name, E-Mail, Stadt, Adresse, ROW_NUMBER () OVER (PARTITION BY Dep_ID, Name, E-Mail, Stadt, Adresse ORDER BY Dep_ID, Name, E-Mail, Stadt, Adresse) row_no FROM test_database.dbo.Employee ) DELETE FROM cte_delete WHERE row_no> 1;

Die Ausgabe erfolgt wie folgt.

Methode 2: Löschen doppelter Datensätze mit der Funktion „NOT IN ()“

Um nun eine andere Methode zu testen, müssen wir die Tabelle abschneiden, wodurch alle Zeilen aus der Tabelle entfernt werden. Der Befehl insert fügt dann Werte zur Tabelle hinzu. Führen Sie jetzt den folgenden Code aus.

USE [test_database] GO Tabelle abschneiden test_database.dbo.Employee INSERT INTO Employee (Dep_ID, Name, E-Mail, Stadt, Adresse) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', ' 5840 Ne Cornell Rd Hillsboro oder 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003,' Aabharana, Sahni ', '[email protected]', 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]', 'HYATTSVILLE', ' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' [email protected] ',' OMAHA ',' 2975 Crouse Lane Burlington Nc 272150000 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006,' Humbaerto Acevedo ',' [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 ' ), (006, 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7. St. Paul Mn 551063852'), (007, 'Pilar Ackaerman', 'pilar.ackaerman @ gmail.com','ATLANTA','5813 Eastern Ave Hyattsville Md 207822201'); SELECT * FROM Employee

Die Ausgabe erfolgt wie unten angegeben.

Führen Sie den unten angegebenen Code aus, um alle doppelten Zeilen aus der Tabelle "Mitarbeiter" zu löschen.

Löschen Sie FROM test_database.dbo.Employee WHERE ID NOT IN (SELECT MAX (ID) FROM test_database.dbo.Employee GROUP BY Dep_ID, Name, E-Mail, Stadt, Adresse)

Die Ausgabe wird wie folgt sein.

Ausführungsplan und Abfragekosten für das Löschen doppelter Zeilen aus der indizierten Tabelle:

Jetzt müssen wir prüfen, welche Methode kostengünstig ist und weniger Ressourcen benötigt. Wählen Sie den Code aus und klicken Sie auf den Ausführungsplan. Der folgende Bildschirm zeigt alle ausgeführten Pläne zusammen mit dem Kostenprozentsatz.

Wir können sehen, dass Methode 1 "Löschen doppelter Datensätze mit der Funktion" ROW_NUMBER () "" 33% Kosten verursacht und Methode 2 "Löschen doppelter Datensätze mit der Funktion NOT IN ()" 67% Kosten hat. Daher ist die erste Methode im Vergleich zur zweiten Methode am kostengünstigsten.

Entfernen von Duplikaten aus einer SQL Server-Tabelle ohne eindeutigen Index:

Es ist etwas schwieriger, doppelte Zeilen oder Tabellen ohne einen eindeutigen Index zu entfernen. In diesem Szenario hilft uns die Verwendung eines allgemeinen Tabellenausdrucks (CTE) und der Funktion ROW NUMBER () beim Entfernen der doppelten Datensätze. Um Duplikate ohne eindeutigen Index aus der Tabelle zu entfernen, müssen eindeutige Zeilenbezeichner generiert werden.

Führen Sie den folgenden Code aus, um die Tabelle ohne eindeutigen Index zu erstellen.

USE [test_database] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Employee_with_out_index] ([Dep_ID] [int] NULL, [Name] [varchar] (200) NULL, [email] [varchar] (250 ) NULL, [Stadt] [Varchar] (250) NULL, [Adresse] [Varchar] (500) NULL,) GO

Die Ausgabe wird wie folgt sein.

Fügen Sie nun Datensätze in die erstellte Tabelle mit dem Namen "Employee_with_out_index" ein, indem Sie den folgenden Code ausführen.

USE [test_database] GO INSERT INTO Employee_with_out_index (Dep_ID, Name, E-Mail, Stadt, Adresse) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro Or 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center Omaha Ne 681987400 '), (003,' Aabharana, Sahni ',' abharana.sahni@gmail. com ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (003,' Aabharana, Sahni ',' [email protected] ',' HYATTSVILLE ',' 2 Barlo Circle Suite A Dillsburg Pa 170191 '), (004,' Aabish Mughal ',' [email protected] ',' OMAHA ',' 2975 Crouse Lane Burlington Nc 272150000 '), (005,' Aabram Howell ','[email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (005,' Aabram Howell ',' [email protected] ',' DILLSBURG ',' 868 York Ave Atlanta Ga 303102750 '), (006 , 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7. St. Paul Mn 551063852'), (006, 'Humbaerto Acevedo' , '[email protected]', 'SAINT PAUL', '895 E 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]', 'ATLANTA', '5813 Eastern Ave Hyattsville Md 207822201'); SELECT * FROM Employee_with_out_index

Die Ausgabe wird wie folgt sein.

Methode 1: Löschen doppelter Zeilen aus einer Tabelle mit der Funktion „ROW_NUMBER ()“ und JOINS.

Führen Sie den folgenden Code aus, der die Funktion ROW_NUMBER () und JOIN verwendet, um doppelte Zeilen ohne Index aus der Tabelle zu entfernen. Die IT erstellt zunächst eine eindeutige Identität, um allen Zeilen row_no zuzuweisen und nur eine Zeile zu entfernen, um doppelte zu entfernen.

WITH temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (ORDER BY Dep_ID, Name, E-Mail, Stadt, Adresse) AS row_no, Dep_ID, Name, E-Mail, Stadt, Adresse FROM test_database.dbo.Employee_with_out_index) SELECT MAX (row_no) FROM temp_tablr_with_row_ids i WHERE a.Dep_ID = i.Dep_ID und a.Name = i.Name und a.email = i.email und a.city = i.city und a.address = i.address GROUP BY Dep_ID, Name, E-Mail, Stadt, Adresse)

Die Ausgabe wird wie folgt sein.

Methode 2: Löschen doppelter Zeilen aus einer Tabelle mit der Funktion „ROW_NUMBER ()“ und PARTITION BY.

Bei dieser Methode verwenden wir jetzt die Funktion ROW_NUMBER zusammen mit der Klausel partition by, um allen Zeilen row_no zuzuweisen und dann doppelte zu löschen. Zunächst müssen wir dieselbe Tabelle abschneiden, die wir zuvor erstellt haben, damit alle Daten aus der Tabelle gelöscht werden. Fügen Sie dann Datensätze in die Tabelle ein, einschließlich der doppelten Datensätze. Bei der dritten Abfrage werden doppelte Zeilen aus der Tabelle mit dem Namen "Employee_with_out_index" gelöscht.

Tabelle abschneiden Employee_with_out_index INSERT INTO Employee_with_out_index (Dep_ID, Name, E-Mail, Stadt, Adresse) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro Or 97124') , (002, 'Aabdi Maghsoudi', '[email protected]', 'BRENTWOOD', '987400 Nebraska Medical Center Omaha Ne 681987400'), (003, 'Aabharana, Sahni', '[email protected]' , 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]', 'HYATTSVILLE', '2 Barlo Circle Suite A Dillsburg Pa 170191' ), (004, 'Aabish Mughal', '[email protected]', 'OMAHA', '2975 Crouse Lane Burlington Nc 272150000'), (005, 'Aabram Howell', '[email protected]', "DILLSBURG", "868 York Ave Atlanta Ga 303102750"), (005, "Aabram Howell", "[email protected]", "DILLSBURG", "868 York Ave Atlanta Ga 303102750"), (006, " Humbaerto Acevedo ',' [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 '), (006,' Hu mbaerto Acevedo ',' [email protected] ',' SAINT PAUL ',' 895 E 7. St. Paul Mn 551063852 '), (007,' Pilar Ackaerman ',' [email protected] ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 ');

Auswählen doppelter Datensätze in der temporären Tabelle

;; WITH temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (TEILUNG NACH Dep_ID, Name, E-Mail, Stadt, Adresse ORDER BY Dep_ID, Name, E-Mail, Stadt, Adresse) AS row_no, Dep_ID, Name, E-Mail, Stadt, Adresse FROM Employee_with_out_index)

Löschen doppelter Datensätze aus der temporären Tabelle

DELETE a FROM temp_tablr_with_row_ids a WHERE row_no> 1

Die Ausgabe wird wie folgt sein.

Darüber hinaus müssen wir die Kosten für die Ausführung von Abfragen kennen, um zu verstehen, welche Lösung optimiert ist. Sie müssen also alle relevanten Abfragen auswählen und auf den Ausführungsplan klicken. Das folgende Bild zeigt den Ausführungsplan für die Abfragen zusammen mit den Ausführungskosten. Löschabfragen werden im roten Feld hervorgehoben. Die erste Abfrage, die die Klausel "ROW_NUMBER ()" und JOIN verwendet, hat 56% Ausführungskosten, während die zweite Abfrage "ROW_NUMBER ()" und "PARTITION BY" 31% Kosten hat. Die zweite Methode ist also optimierter und wir sollten einer optimierten Lösung folgen.