Semelhanças e Diferenças entre DELETE, TRUNCATE e DROP TABLE

Olá, leitores!
Tudo bem ?

Hoje vamos falar sobre uma dúvida clássica de desenvolvedores e alguns DBA’s que estão começando agora. Afinal, qual a diferença entre DELETE, TRUNCATE TABLE e DROP TABLE ? Quando devemos usar cada um destes comandos ?

Para ilustrar essa diferença, vou apresentar a tabela abaixo:

DELETE TRUNCATE DROP
Apaga os dados de uma tabela Sim Sim Sim
É uma operação de DDL (Exigindo privilégios para tal) Não Sim Sim
É uma operação de DML (Exigindo privilégios para tal) Sim Não Não
Exige bloqueio exclusivo do objeto para ser executado (Nenhuma outra sessão pode estar utilizando esse objeto) Não Sim Sim
Bloqueia o objeto a nível de linha Sim Não Não
Bloqueia o objeto a nível de schema Não Sim Sim
Durante a execução do comando, outras sessões podem fazer leituras no objeto usando HINTS para leitura “suja” (Ex: NOLOCK) Sim Não Não
Geralmente, em tabelas muito grandes e com muita utilização, pode gerar LOCKS e contenções perceptíveis no banco Sim Não Não
Permite selecionar quais dados serão apagados (Não apaga a tabela toda) Sim Não Não
Apaga também a estrutura da tabela, metadados, índices, FK’s, PK. Elimina o objeto do banco de dados. Não Não Sim
Reinicia o auto incremento, recria os índices e desfragmenta a tabela Não Sim Sim
Gera dados na transaction log / Redo log (Oracle), permitindo que os dados sejam restaurados usando backup de log Sim Não Não
Comandos extremamente rápidos de serem executados Não Sim Sim
É possível executar o comando, mesmo que a tabela em questão seja FK de outra tabela Sim Não Não
Possibilidade de utilização em conjunto com Triggers Sim Não Não
Pode ser utilizado em tabelas que são partes de views indexadas (MSSQL) Sim Não Não

Alguns pontos relevantes

  • O comando DROP TABLE apaga a tabela e sua estrutura. O objeto será eliminado do banco. Esse comando não apaga apenas os dados
  • Os comandos DROP TABLE e TRUNCATE TABLE não geram logs detalhados das operações. Eles apenas gravam que o comando foi executado e as páginas afetadas. Por isso, eles ocupam pouquíssimo espaço na transaction log / redo log e são executados tão rapidamente. O lado ruim disso, é que se um dia você precisar fazer um restore imediato usando o log do banco após alguém ter feito um TRUNCATE TABLE, isso não será possível. O comando DELETE, log cada linha que foi deletada, gerando uma quantidade de registros de log muito grande (Devido à cláusula WHERE), dependendo do tamanho da tabela. Isso permite que você possa realizar um restore imediato após alguém ter realizado um DELETE errado, mas pode estourar sua transaction log / redo log se estiver apagando uma quantidade de registros muito grande
  • O comando TRUNCATE TABLE apaga TODOS os registros de uma tabela. Além disso, ele reinicia o auto incremento (se houver), reduz a fragmentação da tabela e índices para 0, quase não gera log e é executado rapidamente no banco, mesmo com tabelas muito grandes. Para rotinas onde todos os dados são apagados e gerados novamente a cada execução, é a solução mais recomendada
  • Uma vez que o TRUNCATE simplesmente exclui todas as páginas e extensões de uma tabela, não seria possível validar se algum desses registros é referenciado por alguma tabela filha. O DELETE loga linha a linha e caso não haja violação de integridade referencial é possível utilizá-lo mesmo em tabelas referenciadas. A opção CASCADE é capaz de propagar as atualizações para o DELETE, mas não para o TRUNCATE uma vez que esse comando não mantém a relação de linhas afetadas e não é portanto capaz de propagar seus efeitos
  • No Oracle Database, existe uma trigger que é disparada no evento “AFTER TRUNCATE ON Database”, que pode ser utilizada após algum comando de TRUNCATE, para logar qual o usuário que executou o comando, por exemplo. Mas não existe trigger específica para antes da execução do comando. Isso pode ser criado utilizando uma trigger que é disparada no evento “BEFORE DDL ON Database”, mas não é uma solução “oficial”
  • As views indexadas materializam dados de uma tabela ou de várias tabelas combinadas. Se o TRUNCATE fosse executado em uma tabela participante, a view indexada simplesmente ficaria inválida, pois os comandos de exclusão individuais não seriam logados e uma falha não permitiria que o banco se recuperasse (não haveria tracking das alterações) para refazer o índice da view
  • Por se tratarem de operações diferentes (DML x DDL), os privilégios exigidos para execução do comando DELETE é diferente dos necessários para DROP TABLE e TRUNCATE TABLE
  • Os comandos DROP TABLE e TRUNCATE são praticamente idênticos em todas as comparações. A única diferença entre ambos, é que o DROP TABLE apaga os objetos e metadados do banco, enquanto o TRUNCATE TABLE apenas deixa a tabela vazia (sem registros)

Exemplos de utilização

Espero ter tirado todas as suas dúvidas sobre esse assunto.
Caso ainda tenha dúvidas, deixe o seu comentário aqui embaixo que irei respondê-la 🙂

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

Deixe uma resposta