Hoe dubbele rijen uit een SQL Server-tabel te verwijderen?

Bij het ontwerpen van objecten in SQL Server moeten we bepaalde best practices volgen. Een tabel moet bijvoorbeeld primaire sleutels, identiteitskolommen, geclusterde en niet-geclusterde indexen, gegevensintegriteit en prestatiebeperkingen hebben. SQL Server-tabel mag geen dubbele rijen bevatten volgens de aanbevolen procedures voor het ontwerpen van databases. Soms hebben we echter te maken met databases waar deze regels niet worden gevolgd of waar uitzonderingen mogelijk zijn wanneer deze regels opzettelijk worden omzeild. Hoewel we de best practices volgen, kunnen we problemen krijgen zoals dubbele rijen.

We zouden dit type gegevens bijvoorbeeld ook kunnen krijgen tijdens het importeren van tussentabellen, en we willen overtollige rijen verwijderen voordat we ze daadwerkelijk aan de productietabellen toevoegen. Bovendien moeten we het vooruitzicht op het dupliceren van rijen niet uit het oog verliezen, omdat dubbele informatie meerdere verwerkingen van verzoeken, onjuiste rapportageresultaten en meer mogelijk maakt. Als we echter al dubbele rijen in de kolom hebben, moeten we specifieke methoden volgen om de dubbele gegevens op te schonen. Laten we in dit artikel enkele manieren bekijken om gegevensduplicatie te verwijderen.

Hoe dubbele rijen uit een SQL Server-tabel te verwijderen?

Er zijn een aantal manieren in SQL Server om dubbele records in een tabel af te handelen op basis van bepaalde omstandigheden, zoals:

Dubbele rijen verwijderen uit een unieke index SQL Server-tabel

U kunt de index gebruiken om de dubbele gegevens in unieke indextabellen te classificeren en vervolgens de dubbele records verwijderen. Eerst moeten we maak een database aan met de naam "test_database", maak vervolgens een tabel "Werknemer" met een unieke index met behulp van de onderstaande code.

USE master GO CREATE DATABASE test_database GO USE [test_database] GO CREATE TABLE Werknemer ( [ID] INT NOT NULL IDENTITY(1,1), [Dep_ID] INT, [Name] varchar(200), [email] varchar (250) NULL , [plaats] varchar(250) NULL, [adres] varchar(500) NULL CONSTRAINT Primary_Key_ID PRIMARY KEY(ID) )

De output is zoals hieronder.

Voeg nu gegevens in de tabel in. We zullen ook dubbele rijen invoegen. De "Dep_ID" 003.005 en 006 zijn dubbele rijen met vergelijkbare gegevens in alle velden, behalve de identiteitskolom met een unieke sleutelindex. Voer de onderstaande code uit.

GEBRUIK [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'); KIES * VAN Medewerker

De uitvoer zal als volgt zijn.

Zoek nu het aantal rijen in de tabel door de volgende code uit te voeren. De functie count(*) telt het aantal rijen.

SELECT Dep_ID,Name,email,city,address,COUNT(*) ASduplicaat_rows_count FROM Employee GROUP BY Dep_ID,Naam,e-mail,plaats,adres

De output is zoals hieronder. Rij nr. (3, 4), (6, 7), (8, 9) gemarkeerd in het rode vak zijn dubbele rijen.

Het is onze taak om uniciteit af te dwingen door duplicaten voor de dubbele kolommen te verwijderen. Het is iets gemakkelijker om dubbele waarden uit de tabel met een unieke index te verwijderen dan om de rijen uit een tabel zonder deze te verwijderen. Hieronder vindt u twee methoden om dit te bereiken. De eerste methode geeft u dubbele rijen uit de tabel met behulp van de "row_number()" -functie, terwijl de tweede methode de "NOT IN" -functie gebruikt. Deze twee methoden hebben hun eigen kosten, die later zullen worden besproken.

Methode 1: Dubbele records selecteren met de functie "ROW_NUMBER ()"

selecteer * from (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.Employee ) x waarbij rij_nr>1

Methode 2: Dubbele records selecteren met de functie "NOT IN ()"

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

Voer de bovenstaande code uit en u ziet de volgende uitvoer. Beide methoden geven hetzelfde resultaat, maar hebben verschillende kosten.

Nu zullen we de hierboven geselecteerde dubbele rijen verwijderen met behulp van "CTE" met behulp van de volgende code. De volgende code selecteert dubbele rijen die moeten worden verwijderd met behulp van de functie "ROW_NUMBER ()".

Methode 1: dubbele records verwijderen met de functie "ROW_NUMBER ()"

WITH cte_delete AS (SELECT Dep_ID,Name,email,city,address, ROW_NUMBER() OVER (VERDELING DOOR Dep_ID,Name,email,city,address ORDER BY Dep_ID,Name,email,city,address) row_no FROM test_database.dbo.Employee ) VERWIJDEREN VAN cte_delete WHERE row_no > 1;

De uitvoer zal zijn zoals hieronder.

Methode 2: Dubbele records verwijderen met de functie "NOT IN ()"

Om nu een andere methode te testen, moeten we de tabel afkappen, waardoor alle rijen uit de tabel worden verwijderd. Vervolgens voegt de opdracht insert waarden toe aan de tabel. Voer nu de volgende code uit.

GEBRUIK [test_database] GO beknot tabel test_database.dbo.Employee 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 Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', 'pilar.ackaerman@ gmail.com','ATLANTA','5813 Eastern Ave Hyattsville Md 207822201'); KIES * VAN Medewerker

De uitvoer zal zijn zoals hieronder weergegeven.

Voer de onderstaande code uit om alle dubbele rijen uit de tabel "Werknemer" te verwijderen.

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

De uitvoer zal als volgt zijn.

Uitvoeringsplan en querykosten voor het verwijderen van dubbele rijen uit de geïndexeerde tabel:

Nu moeten we nagaan welke methode kosteneffectief is en minder middelen kost. Selecteer de code en klik op het uitvoeringsplan. Het volgende scherm zal verschijnen met alle plannen die worden uitgevoerd, samen met het kostenpercentage.

We kunnen zien dat methode 1 "het verwijderen van dubbele records met de functie "ROW_NUMBER ()" 33% kosten heeft en methode 2 "duplicaatrecords verwijderen met de functie NOT IN ()" 67% kosten. Dus methode één is het meest kosteneffectief in vergelijking met methode twee.

Duplicaten verwijderen uit een SQL Server-tabel zonder een unieke index:

Het is wat moeilijker om dubbele rijen of tabellen te verwijderen zonder een unieke index. In dit scenario helpt het gebruik van een algemene tabelexpressie (CTE) en de functie ROW NUMBER() ons bij het verwijderen van de dubbele records. Om duplicaten uit de tabel te verwijderen zonder een unieke index, moeten we unieke rij-ID's genereren.

Voer de volgende code uit om de tabel te maken zonder een unieke index.

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, [plaats] [varchar](250) NULL, [adres] [varchar](500) NULL, ) GO

De uitvoer zal als volgt zijn.

Voeg nu records in de gemaakte tabel met de naam "Employee_with_out_index" in door de volgende code uit te voeren.

GEBRUIK [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 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' , '[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

De uitvoer zal als volgt zijn.

Methode 1: dubbele rijen uit een tabel verwijderen met de functie "ROW_NUMBER ()" en JOINS.

Voer de volgende code uit die de functie ROW_NUMBER () gebruikt en JOIN om dubbele rijen uit de tabel te verwijderen zonder index. IT creëert eerst een unieke identiteit om row_no toe te wijzen aan alle rijen en slechts één rij te behouden door dubbele rijen te verwijderen.

WITH 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 ) VERWIJDER een UIT WHERE_out_index ) SELECTEER MAX(rij_nr) FROM temp_tablr_with_row_ids i WHERE a.Dep_ID=i.Dep_ID en a.Name=i.Name en a.email=i.email en a.city=i.city en a.address=i.address GROUP BY Dep_ID,Naam,e-mail,plaats,adres)

De uitvoer zal als volgt zijn.

Methode 2: dubbele rijen uit een tabel verwijderen met de functie "ROW_NUMBER ()" en PARTITION BY.

In deze methode gebruiken we de functie ROW_NUMBER samen met partitie voor clausule om rij_nr toe te wijzen aan alle rijen en vervolgens dubbele rijen te verwijderen. Allereerst moeten we dezelfde tabel die we eerder hebben gemaakt afkappen, zodat alle gegevens uit de tabel worden verwijderd. Voeg vervolgens records in de tabel in, inclusief de dubbele records. De derde query verwijdert dubbele rijen uit de tabel met de naam "Employee_with_out_index".

tabel afkappen 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 of 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 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]',' ATLANTA','5813 Eastern Ave Hyattsville Md 207822201');

Dubbele records selecteren in de tijdelijke tabel

; WITH 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 )

Dubbele records verwijderen uit de tijdelijke tabel

VERWIJDER a FROM temp_tablr_with_row_ids a WHERE row_no > 1

De uitvoer zal als volgt zijn.

Bovendien moeten we weten wat de uitvoeringskosten van query's zijn om te begrijpen welke een geoptimaliseerde oplossing is. U moet dus alle relevante zoekopdrachten selecteren en op het uitvoeringsplan klikken. De onderstaande afbeelding toont het uitvoeringsplan voor de query's samen met de uitvoeringskosten. Verwijderquery's worden gemarkeerd in het rode vak. De eerste query die "ROW_NUMBER ()" en de JOIN-clausule gebruikt, heeft 56% uitvoeringskosten, terwijl de tweede query "ROW_NUMBER ()" en "PARTITION BY" 31% kosten heeft. Dus de tweede methode is een meer geoptimaliseerde en we zouden een geoptimaliseerde oplossing moeten volgen.