Sådan fjernes duplikerede rækker fra en SQL Server-tabel?

Når vi designer objekter i SQL Server, skal vi følge visse bedste fremgangsmåder. For eksempel skal en tabel have primære nøgler, identitetskolonner, klyngede og uklusterede indekser, dataintegritet og ydeevnebegrænsninger. SQL Server-tabellen bør ikke indeholde duplikatrækker i henhold til bedste praksis i databasedesign. Nogle gange har vi dog brug for databaser, hvor disse regler ikke følges, eller hvor undtagelser er mulige, når disse regler forsætligt omgåes. Selvom vi følger de bedste fremgangsmåder, kan vi blive udsat for problemer som duplikatrækker.

For eksempel kunne vi også få denne type data, mens vi importerer mellemliggende tabeller, og vi vil gerne slette overflødige rækker, før vi faktisk føjer dem til produktionstabellerne. Desuden bør vi ikke forlade udsigten til at duplikere rækker, fordi duplikatoplysninger muliggør flere håndtering af anmodninger, forkerte rapporteringsresultater og mere. Men hvis vi allerede har duplikatrækker i kolonnen, skal vi følge specifikke metoder for at rydde duplikatdataene op. Lad os se på nogle måder i denne artikel for at fjerne dataduplikering.

Sådan fjernes duplikerede rækker fra en SQL Server-tabel?

Der er en række måder i SQL Server til at håndtere duplikerede poster i en tabel baseret på særlige omstændigheder såsom:

Fjernelse af dubletterækker fra en unik SQL Server-tabel

Du kan bruge indekset til at klassificere duplikatdata i unikke indekstabeller og derefter slette duplikatposter. Først skal vi oprette en database kaldet “test_database”, og opret derefter en tabel “Medarbejder” med et unikt indeks ved hjælp af koden nedenfor.

BRUG master GO OPRET DATABASE test_database GO USE [test_database] GO CREATE TABLE Medarbejder ([ID] INT IKKE NULL IDENTITET (1,1), [Dep_ID] INT, [Navn] varchar (200), [email] varchar (250) NULL , [city] varchar (250) NULL, [address] varchar (500) NULL CONSTRAINT Primary_Key_ID PRIMARY KEY (ID))

Outputtet vil være som nedenfor.

Indsæt nu data i tabellen. Vi indsætter også duplikerede rækker. "Dep_ID" 003,005 og 006 er duplikatrækker med lignende data i alle felter undtagen identitetskolonnen med et unikt nøgleindeks. Udfør koden nedenfor.

BRUG [test_database] INDSÆT TIL MEDARBEJDER (Dep_ID, navn, e-mail, by, adresse) VÆRDIER (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro eller 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 7th St Saint 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 '); VÆLG * FRA medarbejder

Outputtet vil være som følger.

Find nu antallet af rækker i tabellen ved at udføre følgende kode. Tællefunktionen (*) tæller antallet af rækker.

VÆLG Dep_ID, navn, e-mail, by, adresse, COUNT (*) SOM duplikat_række_tælling FRA medarbejdergruppe VED Dep_ID, navn, e-mail, by, adresse

Outputtet vil være som nedenfor. Række nr (3, 4), (6, 7), (8, 9) fremhævet i det røde felt er duplikater.

Vores opgave er at håndhæve unikhed ved at fjerne duplikater til duplikatkolonnerne. Det er lidt nemmere at fjerne duplikatværdier fra tabellen med et unikt indeks end at fjerne rækkerne fra en tabel uden den. Nedenfor er to metoder til at opnå dette. Den første metode giver dig duplikerede rækker fra tabellen ved hjælp af funktionen "række_nummer ()", mens den anden metode bruger funktionen "IKKE IN". Disse to metoder har deres egne omkostninger, som vil blive diskuteret senere.

Metode 1: Valg af dubletter ved hjælp af funktionen “ROW_NUMBER ()”

vælg * fra (SELECT Dep_ID, Name, email, city, address, ROW_NUMBER () OVER (PARTITION BY Dep_ID, Name, email, city, address ORDER BY Dep_ID, Name, email, city, address) row_no FROM test_database.dbo. ) x hvor række_nr> 1

Metode 2: Valg af dubletter ved hjælp af funktionen "NOT IN ()"

VÆLG * FRA test_database.dbo. Medarbejder HVOR ID ikke er i (VÆLG MAX (ID) FRA test_database.dbo. Medarbejdergruppe VED Dep_ID, navn, e-mail, by, adresse)

Udfør ovenstående kode, og du vil se følgende output. Begge metoder giver det samme resultat, men de har forskellige omkostninger.

Nu sletter vi de ovennævnte valgte duplikatrækker ved hjælp af "CTE" ved hjælp af følgende kode. Den følgende kode vælger duplikatrækker, der skal slettes ved hjælp af funktionen “ROW_NUMBER ()”.

Metode 1: Sletning af dubletter ved hjælp af funktionen “ROW_NUMBER ()”

MED cte_delete AS (VÆLG Dep_ID, navn, e-mail, by, adresse, ROW_NUMBER () OVER (PARTITION BY Dep_ID, Name, email, city, address ORDER BY Dep_ID, Name, email, city, address) row_no FROM test_database.dbo. ) SLET FRA cte_delete HVOR række_nr> 1;

Outputtet vil være som nedenfor.

Metode 2: Sletning af dublerede poster ved hjælp af "NOT IN ()" - funktionen

For at teste en anden metode er vi nødt til at afkorte tabellen, som fjerner alle rækkerne fra tabellen. Indsæt derefter kommandoen vil tilføje værdier til tabellen. Udfør følgende kode nu.

BRUG [test_database] GO afkortningstabel test_database.dbo. Medarbejder INDSÆT I MEDARBEJDER (Dep_ID, navn, e-mail, by, adresse) VÆRDIER (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, 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7. St. Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', 'pilar.ackaerman @ gmail.com ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 '); VÆLG * FRA medarbejder

Outputtet vil være som angivet nedenfor.

Udfør koden nedenfor for at slette alle de dobbelte rækker fra tabellen "Medarbejder".

Slet FROM test_database.dbo.Employee WHERE ID NOT IN (SELECT MAX (ID) FROM test_database.dbo.Employee GROUP BY Dep_ID, Name, email, city, address)

Outputtet vil være som følger.

Udførelsesplan og forespørgselsomkostninger til sletning af duplikatrækker fra den indekserede tabel:

Nu skal vi kontrollere, hvilken metode der er omkostningseffektiv og tager færre ressourcer. Vælg koden, og klik på udførelsesplanen. Det følgende skærmbillede viser alle udførelsesplaner sammen med omkostningsprocent.

Vi kan se, at metode 1 "sletning af duplikatposter ved hjælp af" ROW_NUMBER () "- funktionen" har 33% omkostninger, og metode 2 "sletning af duplikatposter ved hjælp af NOT IN () -funktionen" har 67% omkostninger. Så metode en er mest omkostningseffektiv sammenlignet med metode to.

Fjernelse af dubletter fra en SQL Server-tabel uden et unikt indeks:

Det er lidt sværere at fjerne dublerede rækker eller tabeller uden et unikt indeks. I dette scenario hjælper brug af et fælles tabeludtryk (CTE) og ROW NUMBER () -funktionen os med at fjerne de dobbelte poster. For at fjerne dubletter fra tabellen uden et unikt indeks skal vi generere unikke rækkeidentifikatorer.

Udfør følgende kode for at oprette tabellen uden et unikt indeks.

BRUG [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, [by] [varchar] (250) NULL, [adresse] [varchar] (500) NULL,) GO

Outputtet vil være som følger.

Indsæt nu poster i den oprettede tabel med navnet “Employee_with_out_index” ved at udføre følgende kode.

BRUG [test_database] INDSÆT IND I Medarbejder_med_out_index (Dep_ID, navn, e-mail, by, adresse) VÆRDIER (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro Eller 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 7th St Saint Paul Mn 551063852'), (006, 'Humbaerto Acevedo' , '[email protected]', 'SAINT PAUL', '895 E 7. St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]', 'ATLANTA', '5813 Eastern Ave Hyattsville Md 207822201'); VÆLG * FRA Medarbejder_med_index

Outputtet vil være som følger.

Metode 1: Sletning af duplikerede rækker fra en tabel ved hjælp af funktionen "ROW_NUMBER ()" og JOINS.

Udfør følgende kode, der bruger ROW_NUMBER () -funktionen og JOIN for at fjerne dublerede rækker fra tabellen uden indeks. IT opretter først en unik identitet, der tildeler række_no til alle rækkerne og kun holder en række fjernelse af duplikater.

MED temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (ORDER BY Dep_ID, Name, email, city, address) AS row_no, Dep_ID, Name, email, city, address FROM test_database.dbo. VÆLG MAX (række_nr) FRA temp_tablr_with_row_ids i WHERE a.Dep_ID = i.Dep_ID og a.Name = i.Name og a.email = i.email og a.city = i.city og a.adresse = i.adresse GROUP BY Dep_ID, navn, e-mail, by, adresse)

Outputtet vil være som følger.

Metode 2: Sletning af dobbelte rækker fra en tabel ved hjælp af funktionen “ROW_NUMBER ()” og PARTITION BY.

Nu bruger vi i denne metode ROW_NUMBER-funktion sammen med partition for klausul for at tildele række_no til alle rækkerne og derefter slette duplikater. Først og fremmest er vi nødt til at afkorte den samme tabel, som vi har oprettet tidligere, så alle data slettes fra tabellen. Indsæt derefter poster i tabellen inklusive duplikater. Den tredje forespørgsel sletter duplikatrækker fra tabellen med navnet "Medarbejder_med_inde_index".

trunkerer tabel Employee_with_out_index INSERT INTO Employee_with_out_index (Dep_ID, Name, email, city, address) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro 9712 eller 97 , (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 7th St Saint Paul Mn 551063852 '), (007,' Pilar Ackaerman ',' [email protected] ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 ');

Valg af duplikatposter i temp-tabellen

; MED temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (PARTITION BY Dep_ID, Name, email, city, address ORDER BY Dep_ID, Name, email, city, address) AS row_no, Dep_ID, Name, email, city, address FROM Employee_with_out_index)

Sletning af dublerede poster fra temp-tabellen

SLET en FRA temp_tablr_with_row_ids en WHERE række_nr> 1

Outputtet vil være som følger.

Desuden har vi brug for at vide om omkostninger til udførelse af forespørgsler for at forstå, hvilken der er en optimeret løsning. Så du skal vælge alle relevante forespørgsler og klikke på udførelsesplanen. Billedet nedenfor viser udførelsesplanen for forespørgslerne sammen med udførelsesomkostningerne. Slet forespørgsler er fremhævet i det røde felt. Den første forespørgsel, der bruger "ROW_NUMBER ()" og JOIN-klausul, har 56% eksekveringsomkostninger, mens den anden forespørgsel bruger "ROW_NUMBER ()", og "PARTITION BY" har 31% omkostninger. Så den anden metode er en mere optimeret, og vi skal følge en optimeret løsning.