Como deletar linhas repetidas SQL Server de maneira fácil

Quem não precisou ainda certamente um dia vai precisar...
Apagar  linhas repetidas SQL Server usando CTE (Commom Table Expressions) é um jeito rápido e muito simples!
Vamos lá:

-- Cria uma tabela que terá as linhas duplicadas

Create Table Funcionarios (Id Int, Nome Varchar(100), Departmento Varchar(50))

-- Insere dados duplicados na tabela (Ids 1 e 4)

insert into Funcionarios values (1, 'João', '.Net')

insert into Funcionarios values (2, 'Arnaldo', '.NET')

insert into Funcionarios values (3, 'Alexandre', 'PHP')

insert into Funcionarios values (4, 'João', '.NET')

insert into Funcionarios values (5, 'Carlos', 'Designer')

-- Lista os registros repetidos considerando as colunas Nome e Departamento

With tblTemp as

(

Select ROW_NUMBER() Over(PARTITION BY Nome, Departmento ORDER BY Nome)

   As RowNumber,* FROM Funcionarios

)

-- Apaga as linhas repetidas, deixando apenas uma delas

Delete From tblTemp Where RowNumber >1

--Apaga a tabela de testes

Drop Table Funcionarios

Fácil né? Sem copias de tabelas, agrupamentos e etc.

Zerar coluna identity SQL Server (Reset Identity)

Algumas vezes é preciso zerar os dados de uma tabela no SQL Server, e para não perder a sequência de IDs, as vezes também é preciso zerar o identity.

Ao invés de recriar a tabela, o simples comando CHECKIDENT pode fazer isso.

Funciona assim:

DBCC CHECKIDENT('NomeDaTabela', RESEED, 0)

Onde:

• 'NomeDaTabela' é o nome da tabela que terá o identity reiniciado;
• RESEED é o comando para reiniciar o identity;
• 0 é o número para o qual você quer que a contagem recomece, no caso zero. Caso queira voltar para outro número, basta indicá-lo.

Nota: Neste exemplo o próximo número do identity será 1, caso queria que seja 101 por exemplo, atribua o valor 100.

Vamos ao exemplo?

1º) Vamos criar uma tabela de funcionários com identity na coluna Id [Funcionarios]:

CREATE TABLE [dbo].[Funcionarios](

[Id] [int] IDENTITY(1,1) NOT NULL, --Indica que a coluna será incremental de 1

[Nome] [varchar](100) NULL,

[Email] [varchar](200) NULL

 CONSTRAINT [PK_Usuario] PRIMARY KEY CLUSTERED (Id) 

)

2º) Vamos inserir alguns registros na tabela de funcionários:

INSERT INTO Funcionarios VALUES ('Ronaldo Luís Nazário de Lima', 'ronaldo.nazario@exemplo.com.br')

INSERT INTO Funcionarios VALUES ('David Luiz', 'david.luiz@exemplo.com.br')

INSERT INTO Funcionarios VALUES ('Edson Arantes do Nascimento', 'edson.pele@exemplo.com.br')

3º) Vamos checar os ids dos registros inseridos:

4º) Vamos checar o identity atual da tabela, utilizando o mesmo comando RESEED mas sem passar o parâmetro numérico.

5º) Vamos deletar os registros e fazer uma nova inserção, que terá o ID 4:

DELETE FROM [Funcionarios]

INSERT INTO Funcionarios VALUES ('Ronaldo Luís Nazário de Lima', 'ronaldo.nazario@exemplo.com.br')

6º) Como não queremos que a tabela comece do id 4, vamos apagar os registros novamente e zerar o seu Id:

DELETE FROM [Funcionarios]

DBCC CHECKIDENT('[Funcionarios]', RESEED, 0) --Repita o comando, porque na 1ª vez o SQL vai mostrar o identity atual

INSERT INTO Funcionarios VALUES ('Ronaldo Luís Nazário de Lima', 'ronaldo.nazario@exemplo.com.br')

SELECT * FROM [Funcionarios]

E é assim que é possível zerar o identity das tabelas do SQL Server.

Útil principalmente quando há revisão em dados e deseja manter Ids iguais entre diferentes ambientes, como de desenvolvimento e produção por exemplo.

Converter várias linhas em uma string/varchar SQL Server

A dica da vez é sobre como converter várias linhas de um resultado de select por exemplo em uma linha só, separado por exemplo do ponto e virgula ";", no SQL Server.

O script é bem simples e pode ser transformado numa função que retorne string (varchar) para ser usada em outras queries.

No exemplo abaixo criei uma tabela de pessoas, com Id, Nome e Idade. No cenário quero recuperar numa única coluna e linha quais as pessoas com idade > 20, separadas por ponto e virgula.

Vamos lá?

--// Cria tabela de exemplo  num banco de dados do SQL Server
Create table Pessoas (Id Int Identity(1,1), Nome Varchar(100), Idade int)

--// Insere alguns valores na tabela para teste
Insert into Pessoas Values ('Andre', 20)
Insert into Pessoas Values ('Beatriz', 22)
Insert into Pessoas Values ('Carlos', 23)

--// Declara variável que receberá a lista (pode ser maior)
Declare @Result Varchar(200)

--// Inicia variável vazia por que varchar não concatena com null
Set @Result = ''

--// Seleciona cada registro que atende o select e concatena com a variável
Select @Result = @Result + Nome + '; '
  From Pessoas
 Where Idade > 20

--// Se houve resultado, retira o último caractere (;)
If @Result <> ''
Begin
   --// Retira o último caractere ";"
   Set @Result = SUBSTRING(@Result, 1, len(@Result)-1)
End

--// Retorna
Select @Result as Pessoas

No exemplo implementei ainda uma regra para tirar o ponto e virgula do final da variável. O resultado é o seguinte:

Coisa simples, mas evita alguns loops, seja com cursor ou tabela temporária, que é a primeira coisa que pensamos diante de uma necessidade como essa.

Simples né? Espero que útil também!

Desabilitar temporariamente foreign key SQL Server

No SQL Server, algumas vezes é preciso alterar dados de tabelas relacionadas e com a existência de foreigns keys se torna uma tarefa impossível, porque quando você tenta fazer update na tabela 1 recebe erro por existirem dados relacionados na tabela 2 e vice e versa.

Para quem precisa, assim como eu eventualmente preciso, fica a dica para desabilitar foreign key temporariamente. (E tem que ser mesmo temporária hein!)

Nota: É possível desabilitar todas as foreign keys de uma vez ou alguma específica, conforme os exemplos:

Todas as foreigns keys
-- 1º) Desabilita TODAS as foreign key de uma tabela SQL Server
ALTER TABLE [NomeDaTabele] NOCHECK CONSTRAINT ALL

-- 2º) Habilita TODAS as foreign key de uma tabela SQL Server novamente
ALTER TABLE [NomeDaTabela] CHECK CONSTRAINT ALL


Uma foreign key específica
-- 1º) Desabilita uma foreign key específica de uma tabela SQL Server
ALTER TABLE [NomeDaTabela] NOCHECK CONSTRAINT [NomeDaConstraint]

-- 2º) Habilita uma foreign key específica de uma tabela SQL Server
ALTER TABLE [NomeDaTabela] CHECK CONSTRAINT [NomeDaConstraint]


Mais uma vez atento para não esquecer de habilitar a foreign key depois.

É isso aí, simples e objetivo! Espero que também seja útil!