Wenn wir Objekte in SQL Server entwerfen, Wir müssen bestimmte Best Practices befolgen. Beispielsweise, eine Tabelle muss Primärschlüssel haben, Identitätsspalten, Clustered- und Nonclustered-Indizes, Datenintegrität und Leistungseinschränkungen. Die SQL Server-Tabelle sollte gemäß den Best Practices für das Datenbankdesign keine doppelten Zeilen enthalten. jedoch, manchmal ist es notwendig, sich mit Datenbanken zu befassen, in denen diese Regeln nicht befolgt werden oder bei denen Ausnahmen gemacht werden können, wenn diese Regeln absichtlich umgangen werden. Obwohl wir Best Practices befolgen, Wir können auf Probleme wie doppelte Warteschlangen stoßen.
Beispielsweise, Wir könnten diese Art von Daten auch beim Importieren von Zwischentabellen erhalten und möchten redundante Zeilen entfernen, bevor wir sie zu Produktionstabellen hinzufügen. Was ist mehr, wir dürfen nicht auf die Möglichkeit verzichten, Zeilen zu duplizieren, da doppelte Informationen eine mehrfache Bearbeitung von Anfragen ermöglichen, falsche Ergebnisse präsentieren und vieles mehr. jedoch, wenn wir bereits doppelte Zeilen in der Spalte haben, wir müssen bestimmte Methoden befolgen, um doppelte Daten zu bereinigen. Sehen wir uns in diesem Artikel einige Möglichkeiten an, um Datenduplizierungen zu vermeiden.
Die Tabelle, die die doppelten Zeilen enthält.
So entfernen Sie doppelte Zeilen aus einer SQL Server-Tabelle?
Es gibt mehrere Möglichkeiten in SQL Server, doppelte Datensätze in einer Tabelle basierend auf bestimmten Umständen zu behandeln, wie z:
Löschen Sie doppelte Zeilen aus einer einzelnen SQL Server-Indextabelle
Sie können den Index verwenden, um die doppelten Daten in eindeutige Indextabellen zu sortieren und dann die doppelten Datensätze zu entfernen. Zuerst, Wir müssen eine Datenbank namens . erstellen “test_base”, und dann eine Tabelle erstellen “Mitarbeiter” mit einem eindeutigen Index mit dem folgenden Code.
Benutze den Lehrer GEHEN DATENBANK ERSTELLEN test_database GEHEN BENUTZEN [Testdatenbank] GEHEN CREATE TABLE Mitarbeiter ( KEINE NULL IDENTITÄT (1,1), INT, [Name] varchar(200), [Email] varchar (250) NULL, Varchar (250) NULL, [Richtung] varchar(500) Null PRIMÄRSCHLÜSSELEINSCHRÄNKUNG PRIMÄRSCHLÜSSEL-ID )
Das Ergebnis wird folgendes sein.
Erstellen der Tabelle «Mitarbeiter»
Fügen Sie nun die Daten in die Tabelle ein. Wir werden auch doppelte Zeilen einfügen. Das “Dep_ID” 003,005 Ja 006 sind doppelte Zeilen mit ähnlichen Daten in allen Feldern außer der Identitätsspalte mit eindeutigem Schlüsselindex. Führen Sie den folgenden Code aus.
BENUTZEN [Testdatenbank] GEHEN IN DEN MITARBEITER EINSETZEN(Dep_ID,Name,Email,Stadt, Dorf,Richtung) WERTE (001, $0027Aaaronboy Gutiérrez $ 0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mogul$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); WÄHLEN * MITARBEITER
Das Ergebnis wird folgendes sein.
Daten in die benannte Tabelle einfügen “Mitarbeiter” und Daten aus derselben Tabelle abrufen.
Ermitteln Sie nun die Anzahl der Zeilen in der Tabelle, indem Sie den folgenden Code ausführen. Zählfunktion
AUSWÄHLEN DEP_ID,Name,Email,Stadt, Dorf,Richtung,KONTO(*) AS EMPLOYEE_Duplicated_Rows_count GRUPPE NACH DEP_ID,Name,Email,Stadt, Dorf,Richtung
wird die Anzahl der Zeilen zählen.
Das Ergebnis wird folgendes sein. Die Reihen nicht (3, 4), (6, 7), (8, 9) im roten Kästchen markiert sind Duplikate.
Diese Abbildung hebt doppelte Zeilen hervor, deren row_nicht größer als . ist 1
Unsere Aufgabe besteht darin, die Eindeutigkeit durch das Entfernen von Duplikaten aus doppelten Spalten zu erzwingen. Es ist etwas einfacher, doppelte Werte aus einer Tabelle mit einem eindeutigen Index zu entfernen, als Zeilen aus einer Tabelle ohne diesen zu entfernen. Hier sind zwei Methoden, um es zu erreichen. Die erste Methode gibt Ihnen doppelte Zeilen aus der Tabelle mit der Funktion “Zeilennummer ()”, während die zweite Methode die Funktion verwendet “NICHT IN”. Diese beiden Methoden haben ihre eigenen Kosten, die später erörtert werden..
wählen * von (WÄHLEN ICH WÜRDE, Name, Email, Stadt, Dorf, Richtung, ZEILENNUMMER() AUF ( TEILNAHME VON Dep_ID,Name,Email,Stadt, Dorf,Richtung SORTIEREN NACH Dep_ID,Name,Email,Stadt, Dorf,Richtung ) row_no TESTDATENBANK.dbo.Mitarbeiter) x donde row_no>1
Methode 1: Wählen Sie doppelte Datensätze mit der Funktion “ZEILENNUMMER ()”
WÄHLEN * FROM test_database.dbo.Employee WO ID NICHT IST (MAX. AUSWAHL(ICH WÜRDE) FROM test_database.dbo.Employee GRUPPE NACH DEP_ID, Name, Email, Stadt, Dorf, Richtung)
Methode 2: Auswahl doppelter Datensätze mit der Funktion “NICHT IN ()”
Führen Sie den obigen Code aus und Sie sehen die folgende Ausgabe. Beide Methoden liefern das gleiche Ergebnis, aber sie haben unterschiedliche kosten.
Wählen Sie doppelte Zeilen aus der benannten Tabelle aus “Mitarbeiter” mit Methode 1 Ja 2 beziehungsweise
Jetzt entfernen wir die zuvor ausgewählten doppelten Zeilen mit “Côte” mit dem folgenden Code. Der folgende Code wählt doppelte Zeilen aus, die mit der Funktion entfernt werden sollen “ZEILENNUMMER ()”.
MIT cte_delete AS (
WÄHLEN
ICH WÜRDE, Name, Email, Stadt, Dorf, Richtung,
ZEILENNUMMER() AUF (
TEILNAHME VON
Dep_ID,Name,Email,Stadt, Dorf,Richtung
SORTIEREN NACH
Dep_ID,Name,Email,Stadt, Dorf,Richtung
) row_no
VON
test_database.dbo.Mitarbeiter
)
DELETE FROM cte_borrar WHERE row_no> 1;
Methode 1: Entfernen Sie doppelte Datensätze mit der Funktion “ZEILENNUMMER ()”
Das Ergebnis wird folgendes sein.
Entfernen doppelter Datensätze aus der indizierten Tabelle mit der Funktion "ROW_NUMBER" ()
Methode 2: Entfernen Sie doppelte Datensätze mit der Funktion “NICHT IN ()”
BENUTZEN [Testdatenbank] GEHEN kürzen Sie die Tabelle test_database.dbo.Employee IN DEN MITARBEITER EINSETZEN(Dep_ID,Name,Email,Stadt, Dorf,Richtung) WERTE (001, $0027Aaaronboy Gutiérrez $ 0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mogul$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); WÄHLEN * MITARBEITER
Jetzt, eine andere Methode ausprobieren, Wir müssen die Tabelle abschneiden, wodurch alle Zeilen aus der Tabelle entfernt werden. Später, Einfügebefehl fügt der Tabelle Werte hinzu. Führen Sie jetzt den folgenden Code aus.
Das Ergebnis wird das unten angegebene sein.
Daten in die benannte Tabelle einfügen “Mitarbeiter” und Daten aus derselben Tabelle abrufen.
Aus test database.dbo.Employee löschen WO ID NICHT IST (MAX. AUSWAHL(ICH WÜRDE) FROM test_database.dbo.Employee GRUPPE NACH DEP_ID, Name, Email, Stadt, Dorf, Richtung)
Führen Sie den folgenden Code aus, um alle doppelten Zeilen aus der Tabelle zu entfernen “Mitarbeiter”.
Das Ergebnis wird folgendes sein.
Entfernen Sie alle doppelten Zeilen aus der indizierten Tabelle mit dem Namen "Mitarbeiter".
Ausführungsplan und Abfragekosten zum Entfernen doppelter Zeilen aus indizierten Tabellen:
Jetzt müssen wir prüfen, welche Methode rentabler ist und weniger Ressourcen benötigt. Wählen Sie den Code aus und klicken Sie auf den Ausführungsplan. Der folgende Bildschirm erscheint und zeigt alle Ausführungspläne zusammen mit dem Kostenprozentsatz.
Wir sehen, dass die Methode 1 “Entfernen Sie doppelte Datensätze mit der Funktion” ZEILENNUMMER () “kostet 33% und Methode 2” Entfernen Sie doppelte Datensätze mit der Funktion NOT IN () “kostet 67%. Deshalb, Methode eins ist im Vergleich zu Methode zwei am profitabelsten.
Die Methode 1 kostet 33% und die Methode 2 kostet 67%, was zeigt, dass die Methode 1 ist profitabler.
Entfernen Sie Duplikate aus einer SQL Server-Tabelle ohne eindeutigen Index:
Es ist etwas schwieriger, doppelte Zeilen oder Tabellen ohne einen eindeutigen Index zu löschen. In dieser Phase, einen gemeinsamen Tabellenausdruck verwenden (Côte) und die Funktion ZEILENNUMMER () hilft uns, doppelte Datensätze zu beseitigen. Um Duplikate ohne eindeutigen Index aus der Tabelle zu entfernen, wir müssen eindeutige Zeilenbezeichner generieren.
BENUTZEN [Testdatenbank] GEHEN EINGEBEN ANSI_NULLS IN GEHEN QUOTED_IDENTIFIER EINGEBENER GEHEN TABELLE ERSTELLEN [dbo]. [Mitarbeiter ohne Index]( [Dep_ID] [int] NULL, [Name] [varchar](200) NULL, [Email] [varchar](250) NULL, NULL, [Richtung] [varchar](500) NULL, ) GEHEN
Führen Sie den folgenden Code aus, um die Tabelle ohne eindeutigen Index zu erstellen.
Das Ergebnis wird folgendes sein.
Erstellen der Tabelle namens “Employee_with_no_index” ohne eindeutigen Index
BENUTZEN [Testdatenbank] GEHEN EINFÜGEN MIT MITARBEITER_CON_SIN_INDICE(Dep_ID,Name,Email,Stadt, Dorf,Richtung) WERTE (001, $0027Aaaronboy Gutiérrez $ 0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mogul$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); WÄHLEN * FROM Employee_with_out_index
Fügen Sie nun die Datensätze in die erstellte Tabelle namens ein “Employee_with_out_index” Ausführen des folgenden Codes.
Das Ergebnis wird folgendes sein.
Daten in die Tabelle mit einem Ausgabeindex namens index einfügen “Employee_with_no_index”
Methode 1: Entfernen Sie doppelte Zeilen aus einer Tabelle mit der Funktion “ZEILENNUMMER ()” und MITGLIED.
CON temp_tablr_con_row_ids AS ( REIHENNUMMER WÄHLEN() AUF (SORTIEREN NACH DEP_ID,NAME,EMAIL,STADT, DORF,RICHTUNG) AS-Warteschlangennummer, Dep_ID,Name,Email,Stadt, Dorf,Richtung FROM test_database.dbo.Employee_with_out_index ) BORRAR una de temp_tablr_ con_row_ids a WO row_no < (WÄHLEN MAX(row_no) FROM temp_tablr_with_row_ids i WO a.Dep_ID=i.Dep_ID y a.Name=i.Name y a.email=i.email y a.city=i.city y a.address=i.address GRUPO POR DEP_ID, Name, Email, Stadt, Dorf, Richtung)
Führen Sie den folgenden Code aus, der die Funktion ROW_NUMBER verwendet () und JOIN, um doppelte Zeilen aus der Tabelle ohne Index zu entfernen. Erstellen Sie zuerst eine eindeutige Identität, um allen Zeilen row_no zuzuweisen und behalten Sie nur eine Zeile bei, indem Sie Duplikate entfernen.
Das Ergebnis wird folgendes sein.
Entfernen doppelter Zeilen aus einer Tabelle ohne Index mit der Funktion “ZEILENNUMMER ()” y MITGLIED
Methode 2: Entfernen Sie doppelte Zeilen aus einer Tabelle mit der Funktion “ZEILENNUMMER ()” und PARTITION BY.
kürze die Employee_with_out_index-Tabelle EINFÜGEN MIT MITARBEITER_CON_SIN_INDICE(Dep_ID,Name,Email,Stadt, Dorf,Richtung) WERTE (001, $0027Aaaronboy Gutiérrez $ 0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027€, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mogul$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St. Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027);
Jetzt, bei dieser Methode, Wir verwenden die ROW_NUMBER-Funktion zusammen mit der partition by-Klausel, um allen Zeilen row_no zuzuweisen und dann Duplikate zu entfernen. Zuerst, Wir müssen dieselbe Tabelle, die wir zuvor erstellt haben, abschneiden, damit alle Daten aus der Tabelle entfernt werden. Dann füge die Datensätze in die Tabelle ein, einschließlich doppelter Datensätze. Die dritte Abfrage entfernt die doppelten Zeilen aus der Tabelle namens “Employee_with_no_index”.
; CON temp_tablr_with_row_ids AS ( ROUTENUMMER WÄHLEN() AUF (TEILNAHME NACH DEP_ID,NAME,EMAIL,STADT, DORF,RICHTUNG BESTELLEN NACH Dep_ID,Name,Email,Stadt, Dorf,Richtung) AS row_no, Dep_ID,Name,Email,Stadt, Dorf,Richtung VON Employee_sin_indexar )
Auswählen doppelter Datensätze in der Temperaturtabelle.
DELETE a FROM temp_tablr_with_row_ids a WHERE row_no> 1
Eliminierung doppelter Datensätze aus der Temperaturtabelle.
Das Ergebnis wird folgendes sein.
Kürzung, Einfügen, Entfernen doppelter Zeilen aus einer Tabelle ohne Index und Auswählen der resultierenden Datensätze.
Was ist mehr, Wir müssen die Kosten für die Abfrageausführung kennen, um zu verstehen, was eine optimierte Lösung ist. Deshalb, Sie müssen alle relevanten Abfragen auswählen und auf Ausführungsplan klicken. Die folgende Abbildung zeigt den Abfrageausführungsplan zusammen mit den Ausführungskosten. Löschanfragen sind im roten Feld markiert. Die erste Abfrage, die Sie verwenden “ZEILENNUMMER ()” und die JOIN-Klausel hat Ausführungskosten von 56%, während die zweite Abfrage verwendet “ZEILENNUMMER ()” Ja “TEILUNG VON” kostet 31%. Dann, die zweite Methode ist optimierter und wir müssen einer optimierten Lösung folgen.
Die erste Abfrage, die Sie verwenden “ZEILENNUMMER ()” und die JOIN-Klausel hat Ausführungskosten von 56%, während die zweite Abfrage, die Sie verwenden “ZEILENNUMMER ()” Ja “TEILUNG VON” kostet 31%. Die zweite Methode ist also eine optimiertere
