DELETE, TRUNCATE e DROP TABLE Suas Semelhanças e Diferenças ..

Hoje gostaríamos de compartilhar sobre uma dúvida clássica de desenvolvedores e alguns DBA’s que estão começando a se aprofundar. E então, qual a diferença entre Delete, Truncate Table e Drop Table? 

Quando e qual as melhores situações que devemos usar cada um destes comandos ?

Para melhor exemplificar essas diferenças, ilustramos com a tabela abaixo:

/* Apagando a tabela "clientes" e seus dados, sua primary key, 
   as foreign keys e índices */
  DROP TABLE dbo.CLIENTES

/* Apagando todos os dados da tabela "clientes"  
   e reduzindo a fragmentação dos índices e tabelas para 0 */
  TRUNCATE TABLE dbo.CLIENTES

/* Apagando os dados de clientes que são do estado de São Paulo. 
   Neste caso, cada linha apagada será logada e a fragmentação
   dos índices e tabelas não será alterado */
  DELETE FROM dbo.CLIENTES WHERE UF = 'SP'


Considerações finais:

• 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)