Rimuovere righe duplicate da una tabella SQL Server usando uno script

  • Articolo

Questo articolo fornisce uno script che è possibile usare per rimuovere righe duplicate da una tabella in Microsoft SQL Server.

Versione originale del prodotto: SQL Server
Numero originale della Knowledge Base: 70956

Riepilogo

Esistono due metodi comuni che è possibile usare per eliminare record duplicati da una tabella SQL Server. Per una dimostrazione, iniziare creando una tabella e dati di esempio:

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)

Provare quindi i metodi seguenti per rimuovere le righe duplicate dalla tabella.

Metodo 1

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 duplicata.
  • 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 ha una colonna IDENTITY, è necessario usare SET IDENTITY_INSERT ON quando si ripristinano i dati nella tabella originale.

Metodo 2

La funzione ROW_NUMBER introdotta in Microsoft SQL Server 2005 rende questa operazione molto più semplice:

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 di eliminazione dei duplicati richiede la scelta dei record da eliminare e dei record da mantenere in base all'ordinamento di altre colonne, è possibile usare l'espressione ORDER BY per eseguire questa operazione.

Ulteriori informazioni

Il metodo 2 è semplice ed efficace per questi motivi:

  • Non richiede la copia temporanea dei record duplicati in un'altra tabella.
  • Non richiede l'unione della tabella originale con se stessa, ad esempio tramite 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 .

Tuttavia, questo metodo non funziona nelle versioni obsolete di SQL Server che non supportano la funzione ROW_NUMBER. In questo caso, è consigliabile usare invece il metodo 1 o un metodo simile.