Remover linhas duplicadas de uma tabela do SQL Server usando um script

  • Artigo

Este artigo fornece um script que você pode usar para remover linhas duplicadas de uma tabela no Microsoft SQL Server.

Versão original do produto: SQL Server
Número original do KB: 70956

Resumo

Há dois métodos comuns que você pode usar para excluir registros duplicados de uma tabela do SQL Server. Para demonstração, comece criando uma tabela e dados de exemplo:

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)

Em seguida, tente os métodos a seguir para remover as linhas duplicadas da tabela.

Método 1

Execute o seguinte script:

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

Este script realiza as ações a seguir na ordem indicada:

  • Move uma instância de qualquer linha duplicada na tabela original para uma tabela duplicada.
  • Exclui todas as linhas da tabela original que também estão localizadas na tabela duplicada.
  • Move as linhas na tabela duplicada de volta para a tabela original.
  • Descarta a tabela duplicada.

Este método é simples. No entanto, ele exige que você tenha espaço suficiente disponível no banco de dados para criar temporariamente a tabela duplicada. Esse método também gera sobrecarga porque você está movendo os dados.

Além disso, se a tabela tiver uma coluna IDENTITY, você precisará usar SET IDENTITY_INSERT ON ao restaurar os dados para a tabela original.

Método 2

A função ROW_NUMBER introduzida no Microsoft SQL Server 2005 torna essa operação muito mais simples:

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

Este script realiza as ações a seguir na ordem indicada:

  • Usa a função ROW_NUMBER para particionar os dados com base em key_value, que pode ser uma ou mais colunas separadas por vírgulas.
  • Exclui todos os registros que receberam um valor DupRank maior que 1. Esse valor indica que os registros estão duplicados.

Por causa da (SELECT NULL) expressão, o script não classifica os dados particionados com base em qualquer condição. Se sua lógica de excluir duplicatas exigir a escolha de quais registros excluir e quais manter com base na ordem de classificação de outras colunas, você poderá usar a ORDER BY expressão para fazer isso.

Mais informações

O método 2 é simples e eficaz por esses motivos:

  • Isso não exige que você copie temporariamente os registros duplicados para outra tabela.
  • Ele não exige que você ingresse na tabela original consigo mesmo (por exemplo, usando uma subconsulta que retorna todos os registros duplicados usando uma combinação de GROUP BY e HAVING).
  • Para obter o melhor desempenho, você deve ter um índice correspondente na tabela que usa a como chave de key_value índice e inclui todas as colunas de classificação que você pode ter usado na ORDER BY expressão.

No entanto, esse método não funciona em versões desatualizadas de SQL Server que não dão suporte à função ROW_NUMBER. Nessa situação, você deve usar o Método 1 ou algum método semelhante.