Como remover linhas duplicadas de uma tabela no SQL Server

Tabelas do Microsoft SQL Server nunca devem conter linhas duplicadas, nem as chaves primárias não exclusivas. Para brevity, irá por vezes referidas chaves primárias como "key" ou "PK" neste artigo, mas esta será sempre indicar "chave primária." PKs duplicados são uma violação de integridade de entidade e devem ser permitidos num sistema relacional. O SQL Server tem vários mecanismos para impor a integridade da entidade, incluindo índices, restrições UNIQUE, PRIMARY KEY restrições e accionadores.

Apesar de, em circunstâncias pouco habituais, poderão ocorrer duplicadas chaves primárias e, caso que devem ser eliminadas. Uma forma que podem ocorrer é se existem PKs duplicados no não relacionais dados fora do SQL Server e importados os dados enquanto não está a ser imposta PK exclusividade. Outra forma que podem ocorrer é através de um erro de estrutura da base de dados, tais como não impor integridade de entidade em cada tabela.

Muitas vezes PKs duplicados são reparados quando tenta criar um índice exclusivo, que vai abortar se forem encontradas chaves duplicadas. Esta mensagem é: Se estiver a utilizar o SQL Server 2000 ou SQL Server 2005, poderá receber a seguinte mensagem de erro: Este artigo descreve como localizar e remover chaves primárias duplicadas de uma tabela. No entanto, deve examinar melhor o processo que permitido duplicados ocorrer de modo a impedir uma periodicidade.

Neste exemplo, utilizaremos a tabela seguinte com valores PK duplicados. Nesta tabela de chave primária é duas colunas (col1, col2). -Não é possível criar um índice exclusivo ou restrição PRIMARY KEY uma vez que duas linhas têm PKs duplicados. Este procedimento ilustra a identificar e remover os duplicados. o primeiro passo consiste em identificar as linhas têm valores de chave primários duplicados: Isto irá devolver uma linha para cada conjunto de valores PK duplicados na tabela. A última coluna neste resultado é o número de duplicados para o valor PK específico.



Se existirem apenas alguns conjuntos de valores PK duplicados, o melhor procedimento é para eliminá-las manualmente numa base individual. Por exemplo: o valor de rowcount deve ser o número de duplicados para um determinado valor de chave n-1. Neste exemplo, existem 2 duplicados para que rowcount estiver definido como 1. Os valores de col1/col2 são retirados da acima GROUP BY resultado da consulta. Se a consulta GROUP BY devolver múltiplas linhas, a consulta "Definir rowcount" tem de ser executada uma vez para cada um destas linhas. Sempre que é executada, defina rowcount para o número de duplicados de determinado valor de PK n-1.

Antes de eliminar as linhas, deve verificar que a linha inteira é duplicada. Embora improvável, é possível que os valores de PK são duplicados, mas a linha como um todo não é. Um exemplo desta situação seria uma tabela com o número da segurança social como chave primária, e ter duas diferentes pessoas (ou linhas) com o mesmo número, cada um com atributos únicos. Neste caso o mau funcionamento provocado chave duplicada pode ter também causado válidos dados exclusivos seja colocada na linha. Estes dados devem ser copiados e mantém para reconciliação estudo e possível antes para eliminar os dados.

Se existirem muitos conjuntos distintos de valores PK duplicados na tabela, poderá demorar demasiado tempo para removê-los individualmente. Neste caso pode ser utilizado o seguinte procedimento:

1. Em primeiro lugar, execute a consulta acima GROUP BY para determinar quantos conjuntos de valores duplicados de PK existirem e a contagem de duplicados para cada conjunto.
2. Seleccione os valores chaves duplicados numa tabela proprietárias. Por exemplo:

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

3. Seleccione as linhas duplicadas numa tabela proprietárias, eliminando duplicados 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, não será o caso, se tinha t1 PKs duplicados, mas as linhas exclusivas (tal como no exemplo SSN acima). Certifique-se que cada chave holddups é exclusivo e que não tem chaves duplicadas, mas as linhas exclusivas. Se assim, deve parar aqui e reconciliar que as linhas que pretende manter para um determinado valor de chave duplicado. Por exemplo, a consulta:

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

   deverá devolver uma contagem de 1 para cada linha. Em caso afirmativo, avance para o passo 5 abaixo. Caso contrário, tem chaves duplicadas, mas as linhas exclusivas e tem de decidir quais as linhas para guardar. Isto normalmente entail ou eliminar uma linha ou criar um novo valor de chave exclusivo para esta linha. Tirar um destes dois passos para cada PK esse duplicados na tabela holddups.
5. Elimine linhas duplicadas da tabela original. Por exemplo:

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

6. Colocar linhas únicas novamente na tabela original. Por exemplo:

   INSERT t1 SELECT * FROM holddups