Ta bort dubblettrader från en SQL Server-tabell med hjälp av ett skript

Den här artikeln innehåller ett skript som du kan använda för att ta bort dubblettrader från en tabell i Microsoft SQL Server.

Ursprunglig produktversion: SQL Server
Original-KB-nummer: 70956

Sammanfattning

Det finns två vanliga metoder som du kan använda för att ta bort dubblettposter från en SQL Server-tabell. Börja med att skapa en exempeltabell och data för demonstration:

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

Prova sedan följande metoder för att ta bort dubblettraderna från tabellen.

Metod 1

Kör följande skript:

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

Skriptet utför följande åtgärder i angiven ordning:

• Flyttar en instans av en dubblettrad i den ursprungliga tabellen till en dubblettabell.
• Tar bort alla rader från den ursprungliga tabellen som också finns i den duplicerade tabellen.
• Flyttar tillbaka raderna i den duplicerade tabellen till den ursprungliga tabellen.
• Släpper dubblettabellen.

Den här metoden är enkel. Det kräver dock att du har tillräckligt med utrymme i databasen för att tillfälligt skapa dubblettabellen. Den här metoden medför också kostnader eftersom du flyttar data.

Om tabellen har en IDENTITY-kolumn måste du också använda SET IDENTITY_INSERT ON när du återställer data till den ursprungliga tabellen.

Metod 2

Funktionen ROW_NUMBER som introducerades i Microsoft SQL Server 2005 gör den här åtgärden mycket enklare:

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1 

Skriptet utför följande åtgärder i angiven ordning:

• Använder ROW_NUMBERfunktionen för att partitionera data baserat på key_value vilka som kan vara en eller flera kolumner avgränsade med kommatecken.
• Tar bort alla poster som har tagit emot ett DupRank värde som är större än 1. Det här värdet anger att posterna är dubbletter.

På grund av (SELECT NULL) uttrycket sorterar skriptet inte partitionerade data baserat på något villkor. Om din logik för att ta bort dubbletter kräver att du väljer vilka poster som ska tas bort och vilka som ska behållas baserat på sorteringsordningen för andra kolumner, kan du använda ORDER BY uttrycket för att göra detta.

Mer information

Metod 2 är enkel och effektiv av följande skäl:

• Det kräver inte att du tillfälligt kopierar de duplicerade posterna till en annan tabell.
• Det kräver inte att du ansluter den ursprungliga tabellen med sig själv (till exempel genom att använda en underfråga som returnerar alla dubblettposter med hjälp av en kombination av GROUP BY och HAVING).
• För bästa prestanda bör du ha ett motsvarande index i tabellen som använder key_value som indexnyckel och innehåller eventuella sorteringskolumner som du kan ha använt i ORDER BY uttrycket.

Den här metoden fungerar dock inte i inaktuella versioner av SQL Server som inte stöder funktionen ROW_NUMBER. I det här fallet bör du använda metod 1 eller någon liknande metod i stället.