Hvordan fjerne dupliserte rader fra en SQL Server-tabell?

Når vi designer objekter i SQL Server, må vi følge visse gode fremgangsmåter. For eksempel bør en tabell ha primærnøkler, identitetskolonner, klyngede og uklusterte indekser, dataintegritet og ytelsesbegrensninger. SQL Server-tabellen skal ikke inneholde dupliserte rader i henhold til beste praksis i databasedesign. Noen ganger må vi imidlertid håndtere databaser der disse reglene ikke følges, eller hvor unntak er mulig når disse reglene forsettlig blir omgått. Selv om vi følger de beste fremgangsmåtene, kan vi møte problemer som dupliserte rader.

For eksempel kan vi også få denne typen data mens vi importerer mellomliggende tabeller, og vi vil slette overflødige rader før vi faktisk legger dem til i produksjonstabellene. Videre bør vi ikke overlate muligheten for å duplisere rader fordi duplikatinformasjon tillater flere håndtering av forespørsler, feil rapporteringsresultater og mer. Imidlertid, hvis vi allerede har dupliserte rader i kolonnen, må vi følge spesifikke metoder for å rydde opp dupliserte data. La oss se på noen måter i denne artikkelen for å fjerne dataduplisering.

Hvordan fjerne dupliserte rader fra en SQL Server-tabell?

Det er flere måter i SQL Server å håndtere dupliserte poster i en tabell basert på spesielle omstendigheter, for eksempel:

Fjerne dupliserte rader fra en unik indeks SQL Server-tabell

Du kan bruke indeksen til å klassifisere dupliserte data i unike indekstabeller og deretter slette duplikatposter. Først må vi opprette en database kalt "test_database", og opprett deretter en tabell "Ansatt" med en unik indeks ved å bruke koden nedenfor.

BRUK master GO CREATE DATABASE test_database GO USE [test_database] GO CREATE TABLE Ansatt ([ID] INT IKKE NULL IDENTITET (1,1), [Dep_ID] INT, [Navn] varchar (200), [e-post] varchar (250) NULL , [by] varchar (250) NULL, [adresse] varchar (500) NULL CONSTRAINT Primary_Key_ID PRIMARY KEY (ID))

Resultatet blir som nedenfor.

Sett nå inn data i tabellen. Vi vil også sette inn dupliserte rader. “Dep_ID” 003,005 og 006 er dupliserte rader med lignende data i alle felt unntatt identitetskolonnen med en unik nøkkelindeks. Utfør koden nedenfor.

BRUK [test_database] GO INSERT INTO Employee (Dep_ID, Name, email, city, address) 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 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 '); VELG * FRA ansatt

Resultatet blir som følger.

Finn nå antall rader i tabellen ved å utføre følgende kode. Tellefunksjonen (*) teller antall rader.

VELG Dep_ID, navn, e-postadresse, by, adresse, COUNT (*) SOM duplikat_rute_tall FRA ansattGRUPPE VED Dep_ID, navn, e-post, by, adresse

Resultatet blir som nedenfor. Rad nr (3, 4), (6, 7), (8, 9) uthevet i den røde boksen er dupliserte.

Vår oppgave er å håndheve unikhet ved å fjerne duplikater for duplikatkolonnene. Det er litt lettere å fjerne dupliserte verdier fra tabellen med en unik indeks enn å fjerne radene fra en tabell uten den. Nedenfor er to metoder for å oppnå dette. Den første metoden gir deg dupliserte rader fra tabellen ved hjelp av funksjonen "row_number ()", mens den andre metoden bruker "NOT IN" -funksjonen. Disse to metodene har sine egne kostnader som vil bli diskutert senere.

Metode 1: Velge duplikatposter ved hjelp av “ROW_NUMBER ()” -funksjonen

velg * 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 FRA test_database.dbo. ) x hvor rad_nr> 1

Metode 2: Velge duplikatposter ved hjelp av "NOT IN ()" -funksjonen

VELG * FRA test_database.dbo. Medarbeider HVOR ID IKKE ER I (VELG MAKS (ID) FRA test_database.dbo. MedarbeiderGRUPPE VED Dep_ID, navn, e-postadresse, by, adresse)

Utfør koden ovenfor, og du vil se følgende utdata. Begge metodene gir det samme resultatet, men de har forskjellige kostnader.

Nå vil vi slette de ovennevnte valgte dupliserte radene ved hjelp av "CTE" ved å bruke følgende kode. Den følgende koden velger dupliserte rader som skal slettes ved å bruke “ROW_NUMBER ()” -funksjonen.

Metode 1: Slette dupliserte poster ved hjelp av “ROW_NUMBER ()” -funksjonen

MED cte_delete AS (VELG Dep_ID, navn, e-postadresse, by, adresse, ROW_NUMBER () OVER (DELING BY Dep_ID, Name, email, city, address BESTILL AV Dep_ID, Name, email, city, address) row_no FRA test_database.dbo. Medarbeider ) SLETT FRA cte_delete HVOR row_no> 1;

Resultatet blir som nedenfor.

Metode 2: Slette duplikatposter ved hjelp av “NOT IN ()” -funksjonen

Nå for å teste en annen metode, må vi avkutte tabellen som fjerner alle radene fra tabellen. Deretter legger kommandoen til verdier i tabellen. Utfør følgende kode nå.

BRUK [test_database] GO avkort tabell test_database.dbo.Medarbeider INSERT INTO Employee (Dep_ID, Name, email, city, address) 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, 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7th St. Paul Mn 551063852'), (007, 'Pilar Ackaerman', 'pilar.ackaerman @ gmail.com ',' ATLANTA ',' 5813 Eastern Ave Hyattsville Md 207822201 '); VELG * FRA ansatt

Resultatet blir som angitt nedenfor.

Utfør koden nedenfor for å slette alle dupliserte rader fra tabellen "Ansatt".

Slett 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)

Resultatet blir som følger.

Utførelsesplan og spørringskostnad for å slette dupliserte rader fra den indekserte tabellen:

Nå må vi sjekke hvilken metode som vil være kostnadseffektiv og ta færre ressurser. Velg koden og klikk på utførelsesplanen. Følgende skjermbilde viser alle gjennomføringsplaner sammen med kostnadsprosent.

Vi kan se at metode 1 "sletting av duplikatposter ved hjelp av" ROW_NUMBER () "-funksjonen har 33% kostnad og metode 2" sletting av duplikatposter ved bruk av NOT IN () -funksjonen "har 67% kostnad. Så metoden en er mest kostnadseffektiv sammenlignet med metode to.

Fjerne duplikater fra en SQL Server-tabell uten en unik indeks:

Det er litt vanskeligere å fjerne dupliserte rader eller tabeller uten en unik indeks. I dette scenariet hjelper det å bruke et felles tabelleuttrykk (CTE) og ROW NUMBER () -funksjonen til å fjerne duplikatposter. For å fjerne duplikater fra tabellen uten en unik indeks, må vi generere unike radidentifikatorer.

Utfør følgende kode for å lage tabellen uten en unik indeks.

BRUK [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

Resultatet blir som følger.

Sett nå poster i den opprettede tabellen med navnet “Employee_with_out_index” ved å utføre følgende kode.

BRUK [test_database] GO INSERT INTO Employee_with_out_index (Dep_ID, Name, email, city, address) VALUES (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 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]', 'ATLANTA', '5813 Eastern Ave Hyattsville Md 207822201'); VELG * FRA Medarbeider_med_index

Resultatet blir som følger.

Metode 1: Slette dupliserte rader fra en tabell ved hjelp av “ROW_NUMBER ()” -funksjonen og JOINS.

Utfør følgende kode som bruker ROW_NUMBER () -funksjonen og JOIN for å fjerne dupliserte rader fra tabellen uten indeks. IT oppretter først en unik identitet for å tildele row_no til alle radene og beholde bare en rad for å fjerne 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.Employee_with_out_index) SLETT FRA temp_tablr_with_row_ids VELG MAKS (radnr) 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.address = i.adresse GROUP BY Dep_ID, navn, e-postadresse, by, adresse)

Resultatet blir som følger.

Metode 2: Slette dupliserte rader fra en tabell ved bruk av “ROW_NUMBER ()” -funksjonen og PARTITION BY.

Nå, i denne metoden, bruker vi ROW_NUMBER-funksjonen sammen med partisjon for paragraf for å tildele row_no til alle radene og deretter slette duplikater. Først og fremst må vi kutte den samme tabellen som vi har opprettet tidligere, slik at alle data blir slettet fra tabellen. Deretter setter du inn poster i tabellen inkludert duplikatposter. Det tredje spørsmålet vil slette dupliserte rader fra tabellen med navnet “Employee_with_out_index”.

avkort tabell 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 Or 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 ');

Velge duplikatposter i temp-tabellen

; MED temp_tablr_with_row_ids AS (SELECT ROW_NUMBER () OVER (DELING 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)

Slette dupliserte poster fra temp-tabellen

SLETT en FRA temp_tablr_with_row_ids a WHERE row_no> 1

Resultatet blir som følger.

Videre trenger vi å vite om utførelseskostnader for spørringer for å forstå hvilken som er en optimalisert løsning. Så du må velge alle relevante spørsmål og klikke på utførelsesplanen. Bildet nedenfor viser utførelsesplanen for spørringene sammen med utførelseskostnadene. Slett spørsmål er uthevet i den røde boksen. Det første spørsmålet som bruker “ROW_NUMBER ()” og JOIN-leddet har 56% utførelseskostnader, mens det andre spørringen bruker “ROW_NUMBER ()” og “PARTITION BY” har 31% kostnad. Så den andre metoden er en mer optimalisert, og vi bør følge en optimalisert løsning.