Rimuovere righe duplicate da una tabella SQL Server usando uno script

Versione originale del prodotto: SQL Server
Numero KB originale: 70956

Riepilogo

Questo articolo illustra come rimuovere righe duplicate da una tabella in Microsoft SQL Server usando uno script Transact-SQL (T-SQL). Descrive due metodi comuni: la copia di un'istanza di ogni riga duplicata in una tabella separata e l'uso della ROW_NUMBER funzione per eliminare le righe aggiuntive sul posto. Utilizza questo articolo se una tabella contiene record ripetuti, ad esempio perché non ha vincoli univoci o chiave primaria. Il metodo 2 è più semplice perché funziona sullo stesso oggetto.

Creare una tabella di esempio contenente righe duplicate

Per illustrare entrambi i metodi, creare una tabella di esempio denominata original_table con una colonna, key_valuee aggiungere dati duplicati:

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)

Usare quindi uno dei metodi seguenti per rimuovere le righe duplicate dalla tabella.

Note

Questi script eliminano definitivamente i dati. Eseguire il backup della tabella o del database prima di applicarli ai dati di produzione.

Metodo 1: Spostare righe duplicate in una tabella separata e reinserire una sola copia

Il metodo 1 usa due tabelle: original_table e una seconda tabella, duplicate_table, che lo script crea e quindi elimina. Per applicare lo script a un'altra tabella, sostituire original_table con il nome della tabella. Questo metodo richiede la riscrittura della logica GROUP BY e COUNT se un duplicato è definito da più colonne.

Eseguire lo script seguente:

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

Lo script esegue i seguenti passaggi nell'ordine indicato:

  • Sposta un'istanza di qualsiasi riga duplicata nella tabella originale in una tabella dei duplicati.
  • Elimina tutte le righe dalla tabella originale che si trovano anche nella tabella duplicata.
  • Sposta nuovamente le righe della tabella duplicata nella tabella originale.
  • Elimina la tabella duplicata.

Questo metodo è semplice. Tuttavia, è necessario disporre di spazio sufficiente nel database per compilare temporaneamente la tabella duplicata. Questo metodo comporta anche un sovraccarico perché si spostano i dati.

Inoltre, se la tabella include una colonna IDENTITY , è necessario usare SET IDENTITY_INSERT ON quando si ripristinano i dati nella tabella originale.

Metodo 2: Usare la funzione ROW_NUMBER per eliminare righe duplicate

La funzione ROW_NUMBER, introdotta in Microsoft SQL Server 2005, rende questa operazione molto più semplice. Sostituire key_value con la colonna o le colonne che definiscono un duplicato.

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

Lo script esegue i seguenti passaggi nell'ordine indicato:

  • Usa la funzione ROW_NUMBER per partizionare i dati in base a key_value, che può essere una o più colonne separate da virgole.
  • Elimina tutti i record che hanno ricevuto un valore DupRank maggiore di 1. Questo valore indica che i record sono duplicati.

A causa dell'espressione (SELECT NULL) , lo script non ordina i dati partizionati in base a alcuna condizione. Se la logica per eliminare i duplicati richiede la scelta di quali record eliminare e quali conservare in base all'ordinamento di altre colonne, è possibile usare l'espressione ORDER BY per eseguire questa operazione.

Mantenere la riga più recente quando si rimuovono duplicati

Per mantenere la riga con il valore più recente, ordinare in ordine decrescente in base a tale colonna. Si consideri, ad esempio, un ipotetico employee_table con più righe per lo stesso dipendente. Lo script seguente mantiene solo la riga con il valore più recente updated_date :

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY employee_name
              ORDER BY updated_date DESC
            )
FROM employee_table
) AS T
WHERE DupRank > 1

La riga con il valore più recente updated_date riceve un DupRank valore pari a 1, quindi lo script lo mantiene ed elimina le righe precedenti.

Il metodo 2 è semplice ed efficace per questi motivi:

  • Non è necessario copiare temporaneamente i record duplicati in un'altra tabella.
  • Non è necessario unire la tabella originale con se stessa, ad esempio usando una sottoquery che restituisce tutti i record duplicati usando una combinazione di GROUP BY e HAVING.
  • Per ottenere prestazioni ottimali, è necessario disporre di un indice corrispondente nella tabella che usa key_value come chiave di indice e include tutte le colonne di ordinamento che potrebbero essere state usate nell'espressione ORDER BY .

La ROW_NUMBER funzione è disponibile in SQL Server 2005 e versioni successive. Se si usa una versione di SQL Server che non supporta la funzione ROW_NUMBER, usare invece il metodo 1 o un metodo simile.

Come è possibile evitare righe duplicate in una tabella?

Dopo aver rimosso i duplicati, è possibile evitare nuove righe duplicate definendo un vincolo univoco o un indice univoco nella colonna o nelle colonne che identificano una riga.

Contenuti correlati

  • Ultimo aggiornamento: