SQL Server - Delete e Truncate

  Neste artigo vamos comparar os comandos Delete e Truncate no SQL Server.

Os comandos delete e truncate são usados para remover registros das tabelas, a principal diferença entre eles está na maneira como executam essa operação.

Vamos exbir a seguir as principais diferenças entre delete e truncate :

  Delete Truncate
tipo de comando DML DDL
condição Where suporta não suporta
Reseta a coluna Identity Não Sim
Bloqueio bloqueio de linha bloqueio de tabela e pagina
Log de Transação para cada linha deletada um log indicando a desalocação da página
Desempenho  lento muito mais rápido que delete

Os comandos DML (Linguagem de Manipulação de Dados) lidam com a manipulação de dados como recuperar, modificar, excluir, inserir e atualizar, enquanto os comandos DDL (Linguagem de Definição de Dados) são usados para criar ou modificar a estrutura de objetos de banco de dados.

Mesmo que o comando truncate manipule os dados da tabela removendo as linhas ele é considerado um comando DDL devido ao seu efeito posterior , ou seja:

- Desalocar o espaço usado pela tabela;
– Redefinir a coluna identidade para seu valor Semente;

A operação de truncamento mantém a estrutura da tabela e suas colunas, restrições e índices como estão. Portanto, a operação de truncamento é como redefinir uma tabela para seu estado vazio, portanto, é considerada uma instrução/comando DDL.

A sintaxe destes comandos é dada a seguir:


  TRUNCATE TABLE table_name
 

  DELETE FROM table_name               
 

O comando Truncate removerá todas as linhas da tabela enquanto que Delete vai remover um ou mais registros que correspondam à expressão na condição WHERE definida.

Redefinindo a coluna identidade(Identity)

Em uma tabela no SQL Server podemos especificar a coluna identidade assim:


  column_name int (Seed, Increment)   
 

Suponha que a semente seja 1001 e o incremento/semente seja 1, então o valor inicial da coluna será 1001, depois 1002,1003 e assim por diante. Se adicionarmos 50 registros à tabela, o próximo valor desta coluna de identidade será 1051.

Agora, vamos comparar a atuação dos comandos truncate e delete para remover linhas de uma tabela:


  DELETE FROM table_name
  TRUNCATE TABLE table_name    
 

A coluna identidade da tabela onde aplicamos o comando Truncate será redefinida para seu valor inicial que é 1001 e a coluna identidade da tabela onde aplicamos o comando Delete vai permanecer a mesma.

Assim, se adicionarmos uma linha adicional à tabela que foi truncada, o valor da coluna de identidade será 1001 e o da tabela deletada será 1051.

Comportamento do Log

A operação de exclusão(delete) remove uma linha por vez, para cada linha excluída, o log de transações será criado no log de transações.

Enquanto a operação de truncamento desaloca as páginas de dados da tabela, portanto, o armazenamento também pode ser usado para outras tabelas. Somente essas desalocações são armazenadas no arquivo de log.

Bloqueio do Servidor SQL

Você deve estar ciente dos bloqueios do servidor SQL para entender o próximo ponto. É um mecanismo para impedir que outras operações façam alterações nos dados até a conclusão da operação atual.

O SQL Server pode bloquear os seguintes recursos :

* Linhas
* Dados ou página de índice
* Esquema da tabela
* Tabelas
* Base de dados

A instrução de exclusão(delete) adquire o bloqueio de linha e, portanto, as linhas para a operação de exclusão são bloqueadas de outra operação.

A instrução Truncate adquire bloqueios em tabelas e páginas de dados, não em cada linha. Dessa forma truncate não gravará log para cada registro removido, e, portanto, a operação de truncamento não pode ativar nenhum gatilho de exclusão na tabela.

Restrição de Chave Estrangeira

Se a coluna da sua tabela for referenciada por uma chave estrangeira, você não poderá truncar a tabela, mesmo que a tabela referenciada esteja vazia.

Se você precisar truncar essas tabelas, você precisa dropar a restrição e, em seguida, recriar a restrição após a operação de truncamento.

No caso de operação de exclusão usando Delete, você pode excluir linhas, se não houver referência à linha.

O uso da operação de truncamente de tabelas pode ser feito quando uma operação para alimentar as tabelas com dados iniciais for aplicada.

public static void PopularTabela()
{
        using (var context = new AppDbContext())
        {
            context.Database.ExecuteSqlRaw("truncate table Alunos");
             ...
        }
}

Aqui antes de iniciar estamos executando o comando : truncate na tabela Alunos para a seguir iniciar a carga dos dados.

E estamos conversados...

"De tudo o que se tem ouvido, o fim é: Teme a Deus, e guarda os seus mandamentos; porque isto é o dever de todo o homem."
Eclesiastes 12:13

Referências:

José Carlos Macoratti