Como remover linhas duplicadas de uma tabela no SQL Server

Tabelas do Microsoft SQL Server nunca devem conter linhas duplicadas nem chaves primárias não-exclusivos. Para questões de brevidade, às vezes referirá a chaves primárias como "chave" ou "CP" neste artigo, mas isso irá sempre indicar "chave primária." PKs duplicados são uma violação de integridade de entidade e devem ser permitidos em um sistema relacional. SQL Server possui vários mecanismos para impor integridade de entidade, incluindo índices, restrições UNIQUE, restrições PRIMARY KEY e disparadores.

Apesar de isso, duplicadas chaves primárias podem ocorrer em circunstâncias incomuns e, caso deve ser eliminados. Uma maneira que podem ocorrer é se PKs duplicados existem nos dados não-relacionais fora do SQL Server e os dados são importados enquanto CP exclusividade não está sendo aplicada. Outra maneira de que eles podem ocorrer é por meio de um erro de design de banco de dados, como não impor integridade de entidade em cada tabela.

Com freqüência PKs duplicados são percebidos ao tentar criar um índice exclusivo, que será finalizado se chaves duplicadas forem encontradas. Essa mensagem é: Se você estiver usando o SQL Server 2000 ou SQL Server 2005, você receberá a seguinte mensagem de erro: Este artigo descreve como localizar e remover chaves primárias duplicadas de uma tabela. No entanto, você deve examinar detalhadamente o processo que permitia as acontecer para impedir que uma recorrência de duplicatas.

Neste exemplo, usaremos a tabela a seguir com valores duplicados do CP. Nesta tabela, a chave primária é de duas colunas (col1, col2). Nós não pode criar um índice exclusivo ou a restrição PRIMARY KEY como duas linhas têm PKs duplicados. Este procedimento ilustra como identificar e remover as duplicatas. a primeira etapa é identificar quais linhas têm valores de chave primários duplicados: este retornará uma linha para cada conjunto de valores de CP duplicados na tabela. A última coluna esse resultado é o número de duplicatas para determinado valor CP.



Se houver apenas alguns conjuntos de valores CP duplicados, o melhor procedimento é para excluí-las manualmente em uma base individual. Por exemplo: o valor de número de linhas deve ser o número de duplicatas para um determinado valor chave n-1. Nesse exemplo, há 2 duplicatas para que o número de linhas é definido como 1. Os valores de col1/col2 são tirados do resultado da consulta GROUP BY acima. Se a consulta GROUP BY retorna várias linhas, a consulta "set rowcount" terá a ser executado uma vez para cada uma das seguintes linhas. Cada vez que ele é executado, definidas rowcount para o número de duplicatas de determinado valor CP n-1.

Antes de excluir as linhas, você deve verificar que a linha inteira é duplicada. Embora improvável, é possível que os valores de CP são duplicados, mas a linha como um todo não é. Um exemplo disso seria uma tabela com número da previdência social como chave primária, e cada um com dois diferentes pessoas (ou linhas) com o mesmo número, ter atributos exclusivos. Nesse caso qualquer mau funcionamento causado chave duplicada pode ter também causado válidos exclusivos dados sejam colocados na linha. Esses dados devem ser copiados e preservados para reconciliação de estudo e possível anteriores à exclusão de dados.

Se houver muitos conjuntos distintos de valores de CP duplicados na tabela, pode ser muito demorado para removê-los individualmente. Nesse caso o procedimento a seguir pode ser usado:

1. Primeiro, execute a consulta acima GROUP BY para determinar quantos conjuntos de valores duplicados do CP existem e a contagem de duplicatas para cada conjunto.
2. Selecione os valores chaves duplicados em uma tabela de controladora. Por exemplo:

   SELECT col1, col2, col3=count(*)
   INTO holdkey
   FROM t1
   GROUP BY col1, col2
   HAVING count(*) > 1
   					

3. Selecione as linhas duplicadas em uma tabela controladora, eliminando duplicatas no processo. Por exemplo:

   SELECT DISTINCT t1.*
   INTO holddups
   FROM t1, holdkey
   WHERE t1.col1 = holdkey.col1
   AND t1.col2 = holdkey.col2
   					

4. Neste ponto, a tabela holddups deve ter PKs exclusivos, no entanto, isso não pode ser o caso se t1 tivesse PKs duplicados, mas linhas únicas (como no exemplo SSN acima). Verifique se cada chave em holddups é exclusivo e que você não tem chaves duplicadas, mas linhas únicas. Se assim, você deve parar aqui e reconciliar quais as linhas que deseja manter para um determinado valor de chave duplicado. Por exemplo, a consulta:

   SELECT col1, col2, count(*)
   FROM holddups
   GROUP BY col1, col2
   						

   deve retornar uma contagem de 1 para cada linha. Em caso afirmativo, vá para a etapa 5 abaixo. Se não, você tem chaves duplicadas, mas linhas únicas e precisa decidir quais linhas deseja salvar. Isso geralmente envolvem o descartando uma linha ou criar um novo valor de chave exclusivo para esta linha. Execute uma dessas duas etapas para cada tal CP duplicado na tabela holddups.
5. Exclua linhas duplicadas da tabela original. Por exemplo:

   DELETE t1
   FROM t1, holdkey
   WHERE t1.col1 = holdkey.col1
   AND t1.col2 = holdkey.col2
   					

6. Colocar as linhas exclusivas volta na tabela original. Por exemplo:

   INSERT t1 SELECT * FROM holddups