Rimuovere righe duplicate da una SQL Server tabella utilizzando uno script

In questo articolo viene fornito uno script che è possibile utilizzare per rimuovere righe duplicate da una tabella in Microsoft SQL Server.

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

Riepilogo

Esistono due metodi comuni che è possibile utilizzare per eliminare record duplicati da una SQL Server tabella. 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 a utilizzare 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

Questo script consente di eseguire le azioni seguenti nell'ordine specificato:

  • 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 di nuovo le righe della tabella duplicata nella tabella originale.
  • Elimina la tabella duplicata.

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

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

Metodo 2

La ROW_NUMBER che è stata 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 

Questo script consente di eseguire le azioni seguenti nell'ordine specificato:

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

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

Ulteriori informazioni

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 a se stessa, ad esempio utilizzando una sottoquery che restituisce tutti i record duplicati utilizzando una combinazione di GROUP BY e HAVING.
  • Per ottenere prestazioni ottimali, è necessario disporre di un indice corrispondente nella tabella che utilizza la chiave di indice come e include tutte le colonne di ordinamento che potrebbero essere state utilizzate key_value 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 utilizzare invece il metodo 1 o un metodo simile.