Quando projetamos objetos no SQL Server, devemos seguir certas práticas recomendadas. Por exemplo, uma tabela deve ter chaves primárias, colunas de identidade, índices clusterizados e não clusterizados, integridade de dados e limitações de desempenho. A tabela do SQL Server não deve conter linhas duplicadas de acordo com as práticas recomendadas de design de banco de dados. Porém, às vezes é necessário lidar com bancos de dados onde essas regras não são seguidas ou onde podem ser feitas exceções quando essas regras são intencionalmente contornadas. Embora sigamos as melhores práticas, podemos enfrentar problemas como filas duplicadas.
Por exemplo, Também podemos obter este tipo de dados ao importar tabelas intermediárias e gostaríamos de remover linhas redundantes antes de adicionar às tabelas de produção. O que mais, não devemos abrir mão da possibilidade de duplicar linhas porque informações duplicadas permitem o tratamento múltiplo de solicitações, apresentando resultados incorretos e muito mais. Porém, se já temos linhas duplicadas na coluna, devemos seguir métodos específicos para limpar dados duplicados. Vejamos algumas maneiras neste artigo de eliminar a duplicação de dados.
A tabela que contém as linhas duplicadas.
Como você remove linhas duplicadas de uma tabela do SQL Server?
Existem várias maneiras no SQL Server de lidar com registros duplicados em uma tabela com base em circunstâncias particulares, como:
Exclua linhas duplicadas de uma única tabela de índice do SQL Server
Você pode usar o índice para classificar os dados duplicados em tabelas de índice exclusivas e, em seguida, remover os registros duplicados. Primeiro, precisamos criar um banco de dados chamado “test_base”, e então criar uma mesa “Empregado” com um índice exclusivo usando o seguinte código.
Usar o professor IR CRIAR BANCO DE DADOS test_database IR USAR [test_database] IR CRIAR TABELA Funcionário ( NÃO É UMA IDENTIDADE NULA (1,1), INT, [Nome] varchar(200), [o email] varchar (250) NULO, Varchar (250) NULO, [Morada] varchar(500) Nulo PRIMARY KEY CONSTRAINT ID DA CHAVE PRIMÁRIA )
O resultado será o seguinte.
Criando a tabela «Funcionário»
Agora insira os dados na tabela. Também inseriremos linhas duplicadas. o “Dep_ID” 003,005 e 006 são linhas duplicadas com dados semelhantes em todos os campos, exceto coluna de identidade com índice de chave exclusivo. Execute o seguinte código.
USAR [test_database] IR INSERIR NO FUNCIONÁRIO(Dep_ID,Nome,o email,Cidade,Morada) VALORES (001, $0027Aaaronboy Gutiérrez $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027HILLSBORO $ 0027, $ 00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal $ 0027, $0027abish_mughal @ gmail.com $ 0027, $ 0027OMAHA $ 0027, $ 00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, $0027pilar.ackaerman @ gmail.com $ 0027, $ 0027ATLANTA $ 0027, $ 00275813 Eastern Ave Hyattsville Md 207822201$0027); SELECIONE * EMPREGADO
O resultado será o seguinte.
Insira os dados na tabela chamada “Empregado” e obter dados da mesma tabela.
Agora encontre o número de linhas na tabela executando o seguinte código. Função de contagem
SELECIONE DEP_ID,Nome,o email,Cidade,direção,CONTA(*) AS EMPLOYEE_Duplicate_Rows_count GROUP BY DEP_ID,Nome,o email,Cidade,Morada
contará o número de linhas.
O resultado será o seguinte. As linhas não (3, 4), (6, 7), (8, 9) destacados na caixa vermelha são duplicatas.
Esta figura destaca linhas duplicadas que têm row_no maior que 1
Nossa tarefa é garantir a exclusividade, removendo duplicatas de colunas duplicadas. É um pouco mais fácil remover valores duplicados da tabela com índice único do que remover linhas da tabela sem ele. Aqui estão dois métodos para alcançá-lo. O primeiro método fornece linhas duplicadas da tabela usando a função “número_da_linha ()”, enquanto o segundo método usa a função “NÃO EM”. Esses dois métodos têm seus próprios custos, que serão discutidos posteriormente..
selecionar * a partir de (SELECIONE EU IRIA, Nome, Correio eletrônico, Cidade, Morada, ROW_NUMBER() SOBRE ( PARTICIPAÇÃO DE Dep_ID,Nome,o email,Cidade,Morada ORGANIZAR POR Dep_ID,Nome,o email,Cidade,Morada ) row_no TEST DATABASE.dbo.Employee) x donde row_no>1
Método 1: selecione registros duplicados usando a função “ROW_NUMBER ()”
SELECIONE * FROM test_database.dbo.Employee ONDE ID NÃO ESTÁ (SELEÇÃO MÁXIMA(EU IRIA) FROM test_database.dbo.Employee GROUP BY DEP_ID, Nome, o email, Cidade, Morada)
Método 2: selecionando registros duplicados usando a função “NÃO EM ()”
Execute o código acima e você verá a seguinte saída. Ambos os métodos dão o mesmo resultado, mas eles têm custos diferentes.
Selecione as linhas duplicadas da tabela nomeada “Empregado” usando método 1 e 2 respectivamente
Agora vamos remover as linhas duplicadas selecionadas anteriormente usando “Côte” usando o seguinte código. O código a seguir seleciona linhas duplicadas para remover usando a função “ROW_NUMBER ()”.
COM cte_delete AS (
SELECIONE
EU IRIA, Nome, Correio eletrônico, Cidade, Morada,
ROW_NUMBER() SOBRE (
PARTICIPAÇÃO DE
Dep_ID,Nome,o email,Cidade,Morada
ORGANIZAR POR
Dep_ID,Nome,o email,Cidade,Morada
) row_no
A PARTIR DE
test_database.dbo.Employee
)
DELETE FROM cte_borrar WHERE row_no> 1;
Método 1: remova registros duplicados usando a função “ROW_NUMBER ()”
O resultado será o seguinte.
Remover registros duplicados da tabela indexada usando a função "ROW_NUMBER" ()
Método 2: remova registros duplicados usando a função “NÃO EM ()”
USAR [test_database] IR truncar a tabela test_database.dbo.Employee INSERIR NO FUNCIONÁRIO(Dep_ID,Nome,o email,Cidade,Morada) VALORES (001, $0027Aaaronboy Gutiérrez $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027HILLSBORO $ 0027, $ 00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal $ 0027, $0027abish_mughal @ gmail.com $ 0027, $ 0027OMAHA $ 0027, $ 00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, $0027pilar.ackaerman @ gmail.com $ 0027, $ 0027ATLANTA $ 0027, $ 00275813 Eastern Ave Hyattsville Md 207822201$0027); SELECIONE * EMPREGADO
Agora, tentar outro método, precisamos truncar a tabela, o que removerá todas as linhas da tabela. Mais tarde, o comando insert irá adicionar valores à tabela. Execute o seguinte código agora.
O resultado será o indicado abaixo.
Insira os dados na tabela chamada “Empregado” e obter dados da mesma tabela.
Excluir do banco de dados de teste.dbo.Employee ONDE ID NÃO ESTÁ (SELEÇÃO MÁXIMA(EU IRIA) FROM test_database.dbo.Employee GROUP BY DEP_ID, Nome, o email, Cidade, Morada)
Execute o seguinte código para remover todas as linhas duplicadas da tabela “Empregado”.
O resultado será o seguinte.
Remova todas as linhas duplicadas da tabela indexada chamada "Funcionário
Plano de execução e custo de consulta para remover linhas duplicadas da tabela indexada:
Agora temos que verificar qual método será mais lucrativo e exigirá menos recursos. Selecione o código e clique no plano de execução. A seguinte tela aparecerá mostrando todos os planos de execução junto com a porcentagem de custo.
Podemos ver que o método 1 “remover registros duplicados usando a função” ROW_NUMBER () “tem um custo de 33% e método 2” remover registros duplicados usando a função NOT IN () “tem um custo de 67%. Portanto, o método um é o mais lucrativo em comparação com o método dois.
O método 1 tem um custo de 33% e o método 2 tem um custo de 67%, o que revela que o método 1 é mais lucrativo.
Remova duplicatas de uma tabela do SQL Server sem um índice exclusivo:
É um pouco mais difícil eliminar linhas ou tabelas duplicadas sem um índice exclusivo. Nesta fase, usando uma expressão de tabela comum (Côte) e a função NÚMERO DA LINHA () nos ajuda a eliminar registros duplicados. Para remover duplicatas da tabela sem um índice exclusivo, precisamos gerar identificadores de linha únicos.
USAR [test_database] IR COLOQUE ANSI_NULLS IR COLOQUE QUOTED_IDENTIFIER EM IR CRIAR A TABELA [dbo]. [Funcionário sem índice]( [Dep_ID] [int] NULO, [Nome] [varchar](200) NULO, [o email] [varchar](250) NULO, NULO, [Morada] [varchar](500) NULO, ) IR
Execute o seguinte código para criar a tabela sem um índice exclusivo.
O resultado será o seguinte.
Criando a tabela chamada “Employee_with_no_index” sem um índice único
USAR [test_database] IR INSERT IN EMPLOYEE_CON_SIN_INDICE(Dep_ID,Nome,o email,Cidade,direção) VALORES (001, $0027Aaaronboy Gutiérrez $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027HILLSBORO $ 0027, $ 00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal $ 0027, $0027abish_mughal @ gmail.com $ 0027, $ 0027OMAHA $ 0027, $ 00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, $0027pilar.ackaerman @ gmail.com $ 0027, $ 0027ATLANTA $ 0027, $ 00275813 Eastern Ave Hyattsville Md 207822201$0027); SELECIONE * FROM Employee_with_out_index
Agora insira os registros na tabela criada chamada “Employee_with_out_index” executando o seguinte código.
O resultado será o seguinte.
Insira dados na tabela com um índice de saída chamado “Employee_with_no_index”
Método 1: remover linhas duplicadas de uma tabela usando a função “ROW_NUMBER ()” e JOIN.
CON temp_tablr_con_row_ids AS ( SELECIONE O NÚMERO DA LINHA() SOBRE (CLASSIFICAR POR DEP_ID,NOME,O EMAIL,CIDADE,MORADA) COMO número de fila, Dep_ID,Nome,o email,Cidade,direção FROM test_database.dbo.Employee_with_out_index ) BORRAR una de temp_tablr_ con_row_ids a WHERE row_no < (SELECIONE MÁX(row_no) FROM temp_tablr_with_row_ids i WHERE a.Dep_ID = i.Dep_ID y a.Name = i.Name y a.email = i.email y a.city = i.city y a.address = i.address GRUPO POR DEP_ID, Nome, o email, Cidade, Morada)
Execute o seguinte código que usa a função ROW_NUMBER () e JOIN para remover linhas duplicadas da tabela sem índice. Primeiro crie uma identidade única para atribuir row_no a todas as linhas e manter apenas uma linha removendo duplicatas.
O resultado será o seguinte.
Removendo linhas duplicadas de uma tabela sem um índice usando a função “ROW_NUMBER ()” y JOINS
Método 2: remover linhas duplicadas de uma tabela usando a função “ROW_NUMBER ()” e PARTIÇÃO POR.
truncar a tabela Employee_with_out_index INSERT IN EMPLOYEE_CON_SIN_INDICE(Dep_ID,Nome,o email,Cidade,direção) VALORES (001, $0027Aaaronboy Gutiérrez $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027HILLSBORO $ 0027, $ 00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi $ 0027, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni $ 0027, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal $ 0027, $0027abish_mughal @ gmail.com $ 0027, $ 0027OMAHA $ 0027, $ 00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell $ 0027, $0027aronboy.gutierrez @ gmail.com $ 0027, $ 0027DILLSBURG $ 0027, $ 0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, $0027humbaerto.acevedo @ gmail.com $ 0027, $ 0027 SAINT PAUL $ 0027, $ 0027895 E 7th St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, $0027pilar.ackaerman @ gmail.com $ 0027, $ 0027ATLANTA $ 0027, $ 00275813 Eastern Ave Hyattsville Md 207822201$0027);
Agora, neste método, estamos usando a função ROW_NUMBER junto com partição por cláusula para atribuir row_no a todas as linhas e, em seguida, remover duplicatas. Em primeiro lugar, precisamos truncar a mesma tabela que criamos anteriormente para que todos os dados sejam removidos da tabela. Em seguida, insira os registros na tabela, incluindo registros duplicados. A terceira consulta irá remover as linhas duplicadas da tabela chamada “Employee_with_no_index”.
; CON temp_tablr_with_row_ids AS ( SELECIONE O NÚMERO DA ROTA() SOBRE (PARTICIPAÇÃO POR DEP_ID,NOME,O EMAIL,CIDADE,MORADA ORDER BY Dep_ID,Nome,o email,Cidade,Morada) AS row_no, Dep_ID,Nome,o email,Cidade,Morada FROM Employee_sin_indexar )
Seleção de registros duplicados na tabela de temperatura.
DELETE a FROM temp_tablr_with_row_ids a WHERE row_no> 1
Eliminação de registros duplicados da tabela de temperatura.
O resultado será o seguinte.
Truncamento, inserção, removendo linhas duplicadas de uma tabela sem índice e selecionando os registros resultantes.
O que mais, precisamos saber os custos de execução da consulta para entender o que é uma solução otimizada. Portanto, você precisa selecionar todas as consultas relevantes e clicar no plano de execução. A imagem a seguir mostra o plano de execução da consulta junto com o custo de execução. As consultas de exclusão são destacadas na caixa vermelha. A primeira consulta que você usa “ROW_NUMBER ()” e a cláusula JOIN tem um custo de execução do 56%, enquanto a segunda consulta usa “ROW_NUMBER ()” e “PARTIÇÃO POR” tem um custo de 31%. Então, o segundo método é mais otimizado e devemos seguir uma solução otimizada.
A primeira consulta que você usa “ROW_NUMBER ()” e a cláusula JOIN tem um custo de execução do 56%, enquanto a segunda consulta que você usa “ROW_NUMBER ()” e “PARTIÇÃO POR” tem um custo de 31%. Portanto, o segundo método é mais otimizado
