Hur tar jag bort duplicerade rader från en SQL Server-tabell?

När vi utformar objekt i SQL Server måste vi följa vissa bästa metoder. Till exempel bör en tabell ha primära nycklar, identitetskolumner, klustrade och oklusterade index, dataintegritet och prestandabegränsningar. SQL Server-tabellen bör inte innehålla dubbla rader enligt bästa praxis för databasdesign. Ibland måste vi dock hantera databaser där dessa regler inte följs eller där undantag är möjliga när dessa regler avsiktligt kringgås. Även om vi följer de bästa metoderna, kan vi möta problem som dubbla rader.

Vi kan till exempel också få den här typen av data när vi importerar mellanliggande tabeller, och vi vill ta bort överflödiga rader innan vi faktiskt lägger till dem i produktionstabellerna. Dessutom bör vi inte lämna möjligheten att duplicera rader eftersom duplikatinformation möjliggör flera hanteringar av förfrågningar, felaktiga rapporteringsresultat och mer. Men om vi redan har dubbla rader i kolumnen måste vi följa specifika metoder för att rensa dubblettdata. Låt oss titta på några sätt i den här artikeln för att ta bort duplicering av data.

Hur tar jag bort duplicerade rader från en SQL Server-tabell?

Det finns ett antal sätt i SQL Server att hantera dubbletter av poster i en tabell baserat på särskilda omständigheter som:

Ta bort dubbletterader från en unik SQL Server-tabell för index

Du kan använda indexet för att klassificera duplikatdata i unika indextabeller och sedan ta bort dubbletterna. Först måste vi skapa en databas med namnet "test_database" och skapa sedan en tabell "Anställd" med ett unikt index med hjälp av koden nedan.

ANVÄNDA master GO CREATE DATABASE test_database GO USE [test_database] GO CREATE TABLE Anställd ([ID] INT INTE NULL IDENTITET (1,1), [Dep_ID] INT, [Namn] varchar (200), [email] varchar (250) NULL , [city] varchar (250) NULL, [address] varchar (500) NULL CONSTRAINT Primary_Key_ID PRIMARY KEY (ID))

Resultatet blir som nedan.

Infoga nu data i tabellen. Vi infogar också dubbla rader. ”Dep_ID” 003,005 och 006 är dubbletterader med liknande data i alla fält utom identitetskolumnen med ett unikt nyckelindex. Utför koden nedan.

ANVÄND [test_database] GÅ INSÄTT IN I anställd (Dep_ID, namn, e-postadress, ort, adress) VÄRDEN (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ÄLJ * FRÅN anställd

Produktionen blir följande.

Hitta nu antalet rader i tabellen genom att köra följande kod. Räkningsfunktionen (*) räknar antalet rader.

VÄLJ Dep_ID, namn, e-postadress, ort, adress, COUNT (*) SOM duplicera_rader_antal från anställdsgrupp MED Dep_ID, namn, e-postadress, ort, adress

Resultatet blir som nedan. Rad nr (3, 4), (6, 7), (8, 9) markerade i den röda rutan är dubbletter.

Vår uppgift är att genomdriva unikhet genom att ta bort dubbletter för dubblettkolumnerna. Det är lite lättare att ta bort dubbla värden från tabellen med ett unikt index än att ta bort raderna från en tabell utan den. Nedan ges två metoder för att uppnå detta. Den första metoden ger dig dubbla rader från tabellen med funktionen ”row_number ()”, medan den andra metoden använder “NOT IN” -funktionen. Dessa två metoder har sin egen kostnad som kommer att diskuteras senare.

Metod 1: Välja dubbla poster med funktionen “ROW_NUMBER ()”

välj * från (VÄLJ Dep_ID, Namn, e-post, ort, adress, ROW_NUMBER () ÖVER (DELNING AV Dep_ID, Namn, e-post, stad, adress BESTÄLL AV Dep_ID, Namn, e-post, stad, adress) rad_nr FRÅN test_database.dbo. ) x där rad_nr> 1

Metod 2: Välja dubbla poster med funktionen “INTE IN ()”

VÄLJ * FRÅN test_database.dbo. Anställd VAR ID INTE I (VÄLJ MAX (ID) FRÅN test_database.dbo. Anställd GRUPP AV Dep_ID, namn, e-postadress, ort, adress)

Kör ovanstående kod så ser du följande utdata. Båda metoderna ger samma resultat, men de har olika kostnader.

Nu tar vi bort de ovan valda dubbletteraderna med hjälp av "CTE" med hjälp av följande kod. Följande kod väljer dubbla rader som ska raderas med funktionen “ROW_NUMBER ()”.

Metod 1: Ta bort dubbla poster med funktionen “ROW_NUMBER ()”

MED cte_delete AS (VÄLJ Dep_ID, Namn, e-postadress, ort, adress, ROW_NUMBER () ÖVER (DELNING AV Dep_ID, Namn, e-postadress, ort, adress BESTÄLLNING AV Dep_ID, Namn, e-postadress, ort, adress) row_no FRA test_database.dbo. ) RADERA FRÅN cte_delete VAR rad_nr> 1;

Resultatet blir som nedan.

Metod 2: Ta bort dubbletter med "NOT IN ()" - funktionen

Nu för att testa en annan metod måste vi avkorta tabellen som tar bort alla rader från tabellen. Lägg sedan till kommandot för att lägga till värden i tabellen. Kör följande kod nu.

ANVÄND [test_database] GO trunkerar tabell test_database.dbo.Anställd 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 '); VÄLJ * FRÅN anställd

Resultatet kommer att ges enligt nedan.

Utför koden nedan för att radera alla dubbletterader från tabellen "Anställd".

Ta bort FROM test_database.dbo.Employee WHERE ID NOT IN (VÄLJ MAX (ID) FROM test_database.dbo.AnställdGRUPP MED Dep_ID, namn, e-postadress, ort, adress)

Produktionen blir följande.

Exekveringsplan och frågekostnad för att radera dubbletterader från den indexerade tabellen:

Nu måste vi kontrollera vilken metod som är kostnadseffektiv och tar mindre resurser. Välj koden och klicka på körplanen. Följande skärm visas med alla genomförande planer tillsammans med kostnadsprocent.

Vi kan se att metod 1 "att radera dubbletter med" ROW_NUMBER () "-funktionen" har 33% kostnad och metod 2 "att radera dubbletter med IN-IN () -funktionen" har 67% kostnad. Så metod en är mest kostnadseffektiv jämfört med metod två.

Ta bort dubbletter från en SQL Server-tabell utan ett unikt index:

Det är lite svårare att ta bort dubbla rader eller tabeller utan ett unikt index. I det här scenariot kan vi använda ett gemensamt tabelluttryck (CTE) och ROW NUMBER () -funktionen för att ta bort dubbletterna. För att ta bort dubbletter från tabellen utan ett unikt index måste vi skapa unika radidentifierare.

Kör följande kod för att skapa tabellen utan ett unikt index.

ANVÄND [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, [stad] [varchar] (250) NULL, [adress] [varchar] (500) NULL,) GO

Produktionen blir följande.

Infoga nu poster i den skapade tabellen med namnet "Employee_with_out_index" genom att köra följande kod.

ANVÄND [test_database] GÅ INSÄTT IN I Employee_with_out_index (Dep_ID, namn, e-postadress, ort, adress) VÄRDEN (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'); VÄLJ * FRÅN Anställd_med_ut_index

Produktionen blir följande.

Metod 1: Ta bort dubbla rader från en tabell med funktionen “ROW_NUMBER ()” och JOINS.

Kör följande kod som använder ROW_NUMBER () -funktionen och JOIN för att ta bort dubbletterader från tabellen utan index. IT skapar först en unik identitet för att tilldela rad_nr till alla rader och behålla endast en rad för att ta bort dubbletter.

MED temp_tablr_with_row_ids AS (VÄLJ ROW_NUMBER () OVER (BESTÄLLNING AV Dep_ID, namn, e-post, stad, adress) AS rad_nr, Dep_ID, Namn, e-post, stad, adress FRÅN test_database.dbo. VÄLJ MAX (radnr) FRÅN temp_tablr_with_row_ids i WHERE a.Dep_ID = i.Dep_ID och a.Name = i.Name och a.email = i.email och a.city = i.city och a.adress = i.adress GROUP BY Dep_ID, namn, e-postadress, ort, adress)

Produktionen blir följande.

Metod 2: Ta bort dubbla rader från en tabell med funktionen “ROW_NUMBER ()” och PARTITION BY.

Nu, i den här metoden använder vi ROW_NUMBER-funktionen tillsammans med partition by clause för att tilldela row_no till alla rader och sedan ta bort dubbletter. Först och främst måste vi avkorta samma tabell som vi har skapat tidigare så att all data raderas från tabellen. Infoga sedan poster i tabellen inklusive dubbletter. Den tredje frågan tar bort dubbla rader från tabellen med namnet "Employee_with_out_index".

trunkerar tabell Employee_with_out_index INSERT INTO Employee_with_out_index (Dep_ID, Name, email, city, address) VÄRDEN (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 ');

Välja dubbla poster 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)

Ta bort dubbla poster från temp-tabellen

RADERA FRÅN temp_tablr_with_row_ids a WHERE row_no> 1

Produktionen blir följande.

Dessutom måste vi veta om kostnader för frågekörning för att förstå vilken som är en optimerad lösning. Så du måste välja alla relevanta frågor och klicka på exekveringsplanen. Bilden nedan visar körplanen för frågorna tillsammans med kostnaden för körningen. Radera frågor markeras i den röda rutan. Den första frågan som använder "ROW_NUMBER ()" och JOIN-satsen har 56% exekveringskostnad, medan den andra frågan använder "ROW_NUMBER ()" och "PARTITION BY" har 31% kostnad. Så den andra metoden är mer optimerad och vi bör följa en optimerad lösning.