Qual é a diferença entre TRUNCATE e DELETE no SQL

Eu escrevi uma resposta a esta pergunta por engano, em resposta a uma pergunta sobre a diferença entre DROP e TRUNCATE , mas eu pensei que era uma pena não compartilhar, então eu vou postar minha própria resposta para a minha própria pergunta … é que mesmo ético? 🙂

Edit: Se sua resposta é específica da plataforma, você pode indicar isso.

    Aqui está uma lista de diferenças. Eu destaquei resources específicos do Oracle, e espero que a comunidade possa adicionar a diferença específica de outros fornecedores também. Diferenças comuns à maioria dos fornecedores podem ir diretamente abaixo dos títulos, com diferenças destacadas abaixo.


    Visao geral

    Se você quiser excluir rapidamente todas as linhas de uma tabela, e tiver certeza de que deseja fazê-lo, e não tiver foreign keys contra as tabelas, então um TRUNCATE provavelmente será mais rápido que um DELETE .

    Vários problemas específicos do sistema devem ser considerados, conforme detalhado abaixo.


    Tipo de declaração

    Excluir é DML, Truncar é DDL


    Commit e Rollback

    Variável pelo vendedor

    Servidor SQL

    Truncar pode ser revertido.

    PostgreSQL

    Truncar pode ser revertido.

    Oráculo

    Como um TRUNCATE é DDL, ele envolve dois commits, um antes e outro após a execução da instrução. O truncamento não pode, portanto, ser revertido e uma falha no processo de truncamento emitirá uma confirmação de qualquer maneira.

    No entanto, veja o Flashback abaixo.


    Recuperação de espaço

    Excluir não recupera espaço, Trunque recupera espaço

    Oráculo

    Se você usar a cláusula REUSE STORAGE, os segmentos de dados não serão desalignados, o que pode ser um pouco mais eficiente se a tabela precisar ser recarregada com dados. A marca d’água alta é redefinida.


    Escopo de linha

    Excluir pode ser usado para remover todas as linhas ou apenas um subconjunto de linhas. Truncar remove todas as linhas.

    Oráculo

    Quando uma tabela é particionada, as partições individuais podem ser truncadas isoladamente, portanto, uma remoção parcial de todos os dados da tabela é possível.


    Tipos de object

    Excluir pode ser aplicado a tabelas e tabelas dentro de um cluster. Truncar aplica-se apenas a tabelas ou a todo o cluster. (Pode ser específico do Oracle)


    Identidade do Objeto de Dados

    Oráculo

    Excluir não afeta o ID do object de dados, mas truncar atribui um novo ID de object de dados, a menos que nunca tenha sido inserida na tabela desde sua criação. Mesmo uma única inserção revertida fará com que um novo id de object de dados seja atribuído no truncamento .


    Flashback (Oracle)

    O Flashback funciona em exclusões, mas um truncamento evita o flashback para estados antes da operação.

    No entanto, a partir do 11gR2, o recurso FLASHBACK ARCHIVE permite isso, exceto no Express Edition

    Uso do FLASHBACK no Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638


    Privilégios

    Variável

    Oráculo

    A exclusão pode ser concedida em uma tabela para outro usuário ou function, mas não é possível truncar sem usar uma concessão DROP ANY TABLE.


    Refazer / desfazer

    Excluir gera uma pequena quantidade de refazer e uma grande quantidade de desfazer. Truncar gera uma quantidade insignificante de cada um.


    Índices

    Oráculo

    Uma operação de truncamento torna os índices inúteis utilizáveis ​​novamente. Excluir não.


    Chaves Estrangeiras

    Um truncamento não pode ser aplicado quando uma chave estrangeira ativada faz referência à tabela. O tratamento com exclusão depende da configuração das foreign keys.


    Bloqueio de tabela

    Oráculo

    Truncar requer um bloqueio de tabela exclusivo, a exclusão requer um bloqueio de tabela compartilhada. Portanto, desabilitar os bloqueios de tabela é uma maneira de impedir operações truncadas em uma tabela.


    Gatilhos

    Os gatilhos DML não são triggersdos em um truncamento.

    Oráculo

    Gatilhos DDL estão disponíveis.


    Execução Remota

    Oráculo

    Truncar não pode ser emitido por um link de database.


    Colunas de identidade

    Servidor SQL

    Truncar redefine a seqüência para tipos de coluna IDENTITY, excluir não.


    Conjunto de resultados

    Na maioria das implementações, uma instrução DELETE pode retornar ao cliente as linhas que foram excluídas.

    Por exemplo, em um subprograma Oracle PL / SQL, você poderia:

     DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name; 

    A diferença entre truncar e excluir está listada abaixo:

     +----------------------------------------+----------------------------------------------+ | Truncate | Delete | +----------------------------------------+----------------------------------------------+ | We can't Rollback after performing | We can Rollback after delete. | | Truncate. | | | | | | Example: | Example: | | BEGIN TRAN | BEGIN TRAN | | TRUNCATE TABLE tranTest | DELETE FROM tranTest | | SELECT * FROM tranTest | SELECT * FROM tranTest | | ROLLBACK | ROLLBACK | | SELECT * FROM tranTest | SELECT * FROM tranTest | +----------------------------------------+----------------------------------------------+ | Truncate reset identity of table. | Delete does not reset identity of table. | +----------------------------------------+----------------------------------------------+ | It locks the entire table. | It locks the table row. | +----------------------------------------+----------------------------------------------+ | Its DDL(Data Definition Language) | Its DML(Data Manipulation Language) | | command. | command. | +----------------------------------------+----------------------------------------------+ | We can't use WHERE clause with it. | We can use WHERE to filter data to delete. | +----------------------------------------+----------------------------------------------+ | Trigger is not fired while truncate. | Trigger is fired. | +----------------------------------------+----------------------------------------------+ | Syntax : | Syntax : | | 1) TRUNCATE TABLE table_name | 1) DELETE FROM table_name | | | 2) DELETE FROM table_name WHERE | | | example_column_id IN (1,2,3) | +----------------------------------------+----------------------------------------------+ 

    SOLTA

    O comando DROP remove uma tabela do database. Todas as linhas, índices e privilégios das tabelas também serão removidos. Nenhum gatilho DML será triggersdo. A operação não pode ser revertida.

    TRUNCAR

    TRUNCATE remove todas as linhas de uma tabela. A operação não pode ser revertida e nenhum acionador será triggersdo. Como tal, o TRUCATE é mais rápido e não usa muito espaço de desfazer como um DELETE.

    EXCLUIR

    O comando DELETE é usado para remover linhas de uma tabela. Uma cláusula WHERE pode ser usada para remover apenas algumas linhas. Se nenhuma condição WHERE for especificada, todas as linhas serão removidas. Depois de executar uma operação DELETE, é necessário COMMIT ou ROLLBACK a transação para tornar a alteração permanente ou para desfazê-la. Observe que essa operação fará com que todos os triggersdores DELETE na tabela disparem.

    De: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

    Todas as boas respostas, às quais devo acrescentar:

    Como TRUNCATE TABLE é uma DDL ( Data Defination Language ), não um comando DML ( Data Manipulation Langauge ), os Delete Triggers não são executados.

    Com o SQL Server ou MySQL, se houver um PK com incremento automático, o truncamento redefinirá o contador.

    “Truncar não registra nada” está correto. Eu iria mais longe:

    Truncar não é executado no contexto de uma transação.

    A vantagem de velocidade de truncar sobre exclusão deve ser óbvia. Essa vantagem varia de trivial a enorme, dependendo da sua situação.

    No entanto, vi truncar inadvertidamente a integridade referencial e violar outras restrições. O poder que você ganha modificando dados fora de uma transação tem que ser balanceado contra a responsabilidade que você herda quando você anda na corda bamba sem uma rede.

    Resumo de Excluir Vs truncar no servidor SQL
    Para artigo completo, siga este link: http://codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/

    insira a descrição da imagem aqui

    Extraído do artigo do dotnet mob: Excluir Vs truncar no SQL Server

    Sim, o DELETE é mais lento, o TRUNCATE é mais rápido. Por quê?

    DELETE deve ler os registros, verificar restrições, atualizar o bloco, atualizar índices e gerar refazer / desfazer. Tudo isso leva tempo.

    TRUNCATE simplesmente ajusta um ponteiro no database para a tabela (o High Water Mark) e poof! os dados se foram.

    Isso é específico do Oracle, AFAIK.

    Se acidentalmente você removeu todos os dados da tabela usando Excluir / Truncar. Você pode reverter a transação confirmada. Restaure o último backup e execute o log de transactions até o momento em que Excluir / Truncar está prestes a acontecer.

    As informações relacionadas abaixo são de uma postagem do blog :

    Ao trabalhar no database, estamos usando Excluir e Truncar sem saber as diferenças entre eles. Neste artigo vamos discutir a diferença entre Excluir e Truncar no Sql.

    Excluir:

    • Excluir é um comando DML.
    • A instrução Delete é executada usando um bloqueio de linha, cada linha na tabela é bloqueada para exclusão.
    • Podemos especificar filtros na cláusula where.
    • Exclui os dados especificados se a condição existir.
    • Excluir atividades um gatilho porque a operação é registrada individualmente.
    • Mais lento que truncar porque mantém registros

    Truncar

    • Truncar é um comando DDL.
    • Truncar tabela sempre bloquear a tabela e a página, mas não cada linha.Como remove todos os dados.
    • Não é possível usar a condição Where.
    • Remove todos os dados.
    • A tabela truncada não pode ativar um acionador porque a operação não registra exclusões de linha individuais.
    • Mais rápido no desempenho, porque não mantém nenhum registro.

    Nota: Excluir e Truncar ambos podem ser revertidos quando usados ​​com Transação. Se a transação estiver concluída, significa que não podemos reverter o comando truncar, mas ainda podemos reverter o comando Excluir dos arquivos de log, já que a exclusão de gravação os registra no arquivo de log, caso seja necessário retroceder no futuro dos arquivos de log.

    Se você tiver uma restrição de chave estrangeira referente à tabela que está tentando truncar, isso não funcionará mesmo que a tabela de referência não tenha dados. Isso ocorre porque a verificação de chave estrangeira é feita com DDL em vez de DML. Isso pode ser conseguido desativando temporariamente a (s) restrição (ões) de chave estrangeira para a tabela.

    Excluir tabela é uma operação registrada. Portanto, a exclusão de cada linha é registrada no log de transactions, o que a torna lenta. A tabela truncada também exclui todas as linhas de uma tabela, mas não registra a exclusão de cada linha, mas registra a desalocação das páginas de dados da tabela, o que a torna mais rápida.

    ~ Se acidentalmente você removeu todos os dados da tabela usando Delete / Truncate. Você pode reverter a transação confirmada. Restaure o último backup e execute o log de transactions até o momento em que Excluir / Truncar está prestes a acontecer.

    TRUNCATE é a instrução DDL, enquanto DELETE é uma instrução DML. Abaixo estão as diferenças entre os dois:

    1. Como TRUNCATE é uma instrução DDL ( linguagem de definição de dados ), não requer um commit para tornar as alterações permanentes. E esta é a razão pela qual as linhas excluídas por truncamento não puderam ser recuperadas. Por outro lado, DELETE é uma instrução DML ( linguagem de manipulação de dados ), portanto, requer um commit explícito para tornar seu efeito permanente.

    2. TRUNCATE sempre remove todas as linhas de uma tabela, deixando a tabela vazia e a estrutura da tabela intacta, enquanto DELETE pode remover condicionalmente se a cláusula where for usada.

    3. As linhas excluídas pela instrução TRUNCATE TABLE não podem ser restauradas e você não pode especificar a cláusula where na instrução TRUNCATE .

    4. TRUNCATE não acionam gatilhos ao contrário de no gatilho de exclusão na instrução DELETE

    Aqui está o link muito bom relevante para o tópico.

    TRUNCAR

    • é mais rápido e usa menos resources de log do sistema e de transactions do que o DELETE .
    • remove os dados desalocando as páginas de dados usadas para armazenar os dados da tabela e somente as desalocações de página são registradas no log de transactions.
    • remove todas as linhas de uma tabela, mas a estrutura da tabela, suas colunas, restrições, índices e assim por diante permanecem. O contador usado por uma identidade para novas linhas é redefinido para a semente da coluna.
    • você não pode usar TRUNCATE TABLE em uma tabela referenciada por uma restrição FOREIGN KEY . Porque TRUNCATE TABLE não está logado, ele não pode ativar um gatilho.
    • não pode ser revertida.
    • é o comando DDL.
    • redefine a identidade da tabela

    EXCLUIR

    • remove linhas uma de cada vez e registra uma input no log de transactions para cada linha excluída.
    • Se você quiser manter o contador de identidade, use DELETE . Se você deseja remover a definição de tabela e seus dados, use a instrução DROP TABLE .
    • pode ser usado com ou sem a cláusula WHERE
    • ativa Triggers.
    • pode ser revertida.
    • é o comando DML.
    • não redefine a identidade da tabela.

    Nota: DELETE e TRUNCATE podem ser revertidos quando rodeados por TRANSACTION se a session atual não estiver fechada. Se TRUNCATE for gravado no Editor de Consultas entre TRANSACTION e se a session for fechada, ela não poderá ser revertida, mas DELETE poderá ser revertida.

    No SQL Server 2005, acredito que você pode reverter um truncamento

    EXCLUIR

    O comando DELETE é usado para remover linhas de uma tabela. Uma cláusula WHERE pode ser usada para remover apenas algumas linhas. Se nenhuma condição WHERE for especificada, todas as linhas serão removidas. Depois de executar uma operação DELETE, é necessário COMMIT ou ROLLBACK a transação para tornar a alteração permanente ou para desfazê-la. Observe que essa operação fará com que todos os triggersdores DELETE na tabela disparem.

    TRUNCAR

    TRUNCATE remove todas as linhas de uma tabela. A operação não pode ser revertida e nenhum acionador será triggersdo. Como tal, o TRUCATE é mais rápido e não usa muito espaço de desfazer como um DELETE.

    SOLTA

    O comando DROP remove uma tabela do database. Todas as linhas, índices e privilégios das tabelas também serão removidos. Nenhum gatilho DML será triggersdo. A operação não pode ser revertida.


    DROP e TRUNCATE são comandos DDL, enquanto DELETE é um comando DML. Portanto, as operações DELETE podem ser revertidas (desfeitas), enquanto as operações DROP e TRUNCATE não podem ser revertidas.

    De: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

    Uma pequena correção para a resposta original – delete também gera quantidades significativas de refazer (como o próprio desfazer é protegido por refazer). Isso pode ser visto na saída de autotrace:

     SQL> delete from t1; 10918 rows deleted. Elapsed: 00:00:00.58 Execution Plan ---------------------------------------------------------- 0 DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1) 1 0 DELETE OF 'T1' 2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=43 Card=1) Statistics ---------------------------------------------------------- 30 recursive calls 12118 db block gets 213 consistent gets 142 physical reads 3975328 redo size 441 bytes sent via SQL*Net to client 537 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 10918 rows processed 

    TRUNCATE pode ser revertido se envolvido em uma transação.

    Por favor, veja as duas referências abaixo e teste você mesmo: –

    http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

    http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

    O TRUNCATE vs. DELETE é uma das perguntas infames durante as entrevistas SQL. Apenas certifique-se de explicá-lo corretamente ao entrevistador ou pode custar-lhe o trabalho. O problema é que muitos não estão cientes, então é muito provável que eles considerem a resposta como errada, se você disser a eles que o YES Truncate pode ser revertido.

    A maior diferença é que truncate é uma operação não registrada enquanto delete é.

    Simplesmente significa que, no caso de uma falha no database, você não pode recuperar os dados operados por truncar, mas com a exclusão, você pode.

    Mais detalhes aqui

    Instrução DELETE: Este comando exclui somente as linhas da tabela com base na condição dada na cláusula where ou exclui todas as linhas da tabela se nenhuma condição for especificada. Mas não liberta o espaço que contém a mesa.

    A syntax de uma instrução SQL DELETE é:

    DELETE FROM table_name [condição WHERE];

    Instrução TRUNCATE: este comando é usado para excluir todas as linhas da tabela e liberar o espaço que contém a tabela.

    EXCLUIR

     DELETE is a DML command DELETE you can rollback Delete = Only Delete- so it can be rolled back In DELETE you can write conditions using WHERE clause Syntax – Delete from [Table] where [Condition] 

    TRUNCAR

     TRUNCATE is a DDL command You can't rollback in TRUNCATE, TRUNCATE removes the record permanently Truncate = Delete+Commit -so we can't roll back You can't use conditions(WHERE clause) in TRUNCATE Syntax – Truncate table [Table] 

    Para mais detalhes visite

    http://www.zilckh.com/what-is-the-difference-between-truncate-and-delete/

    Uma diferença adicional das duas operações é que, se a tabela contiver uma coluna de identidade, o contador dessa coluna será reconfigurado 1 (ou para o valor inicial definido para a coluna) em TRUNCATE. DELETE não tem esse efeito.

    Aqui está minha resposta detalhada sobre a diferença entre DELETE e TRUNCATE no SQL Server

    Remover dados : A primeira coisa, ambos podem ser usados ​​para remover as linhas da tabela.
    Mas um DELETE pode ser usado para remover as linhas não apenas de uma Tabela, mas também de uma VIEW ou o resultado de um assunto OPENROWSET ou OPENQUERY sujeito a resources de provedor.

    Cláusula FROM : Com DELETE, você também pode excluir linhas de uma tabela / view / rowset_function_limited com base nas linhas de outra tabela usando outra cláusula FROM. Na cláusula FROM, você também pode escrever condições normais de JOIN. Na verdade, você pode criar uma instrução DELETE a partir de uma instrução SELECT que não contenha nenhuma function agregada substituindo SELECT por DELETE e removendo os nomes das colunas.
    Com TRUNCATE você não pode fazer isso.

    Onde : Um TRUNCATE não pode ter condições WHERE, mas um DELETE pode. Isso significa que com TRUNCATE você não pode excluir uma linha específica ou um grupo específico de linhas. TRUNCATE TABLE é semelhante à instrução DELETE sem cláusula WHERE.

    Desempenho : TRUNCATE TABLE é mais rápido e usa menos resources de log do sistema e de transactions. E uma das razões é travas usadas por qualquer declaração. A instrução DELETE é executada usando um bloqueio de linha, cada linha da tabela é bloqueada para exclusão. TRUNCATE TABLE sempre bloqueia a tabela e a página, mas não cada linha.

    Log de transactions : a instrução DELETE remove as linhas uma por vez e faz inputs individuais no log de transactions de cada linha.
    TRUNCATE TABLE remove os dados desalocando as páginas de dados usadas para armazenar os dados da tabela e registra somente as desalocações de página no log de transactions.

    Páginas : Depois que uma instrução DELETE é executada, a tabela ainda pode conter páginas vazias. TRUNCATE remove os dados desalocando as páginas de dados usadas para armazenar os dados da tabela.

    Trigger : TRUNCATE não ativa os gatilhos de exclusão na tabela. Então você deve ter muito cuidado ao usar TRUNCATE. Nunca se deve usar TRUNCATE se delete Trigger estiver definido na tabela para fazer alguma limpeza automática ou ação de registro quando as linhas forem deletadas.

    Coluna de identidade : com TRUNCATE se a tabela contiver uma coluna de identidade, o contador dessa coluna será redefinido para o valor de semente definido para a coluna. Se nenhuma semente foi definida, o valor padrão 1 será usado. DELETE não redefine o contador de identidade. Então, se você quiser manter o contador de identidade, use DELETE.

    Replicação : DELETE pode ser usado na tabela usada na replicação transacional ou na replicação de mesclagem.
    Enquanto TRUNCATE não pode ser usado contra as tabelas envolvidas na replicação transacional ou replicação de mesclagem.

    Reversão : a instrução DELETE pode ser revertida.
    TRUNCATE também pode ser revertido desde que seja incluído em um bloco TRANSACTION e a session não seja fechada. Uma vez encerrada a session, você não poderá reverter TRUNCATE.

    Restrições : A instrução DELETE poderá falhar se violar um gatilho ou tentar remover uma linha referenciada por dados em outra tabela com uma restrição FOREIGN KEY. Se o DELETE remover várias linhas e qualquer uma das linhas removidas violar um acionador ou uma restrição, a instrução será cancelada, um erro será retornado e nenhuma linha será removida.
    E se DELETE for usado em relação a View, essa View deverá ser uma visão atualizável. TRUNCATE não pode ser usado na tabela usada na exibição Indexada.
    TRUNCATE não pode ser usado contra a tabela referenciada por uma restrição FOREIGN KEY, a menos que uma tabela que tenha uma chave estrangeira que referencia a si mesma.

    Em suma, truncar não registra nada (portanto, é muito mais rápido, mas não pode ser desfeito), enquanto a exclusão é registrada (e pode fazer parte de uma transação maior, será revertida, etc.). Se você tem dados que você não quer em uma tabela no dev normalmente é melhor truncar pois você não corre o risco de preencher o log de transactions

    Um grande motivo para isso ser útil é quando você precisa atualizar os dados em uma tabela de vários milhões de linhas, mas não deseja recriá-los. “Delete *” demoraria uma eternidade, enquanto o impacto de desempenho de Truncate seria insignificante.

    Não é possível fazer DDL em um dblink.

    Eu comentaria no post de matthieu, mas ainda não tenho o representante …

    No MySQL, o contador de incremento automático é redefinido com truncate, mas não com delete.

    Não é que truncar não registra nada no SQL Server. truncate não registra nenhuma informação, mas registra a desalocação da página de dados para a tabela na qual você disparou TRUNCATE.

    e o registro truncado pode ser reverso se definirmos a transação no início e pudermos recuperar o registro truncado após o rollback. Mas não é possível recuperar registros truncados do backup do log de transactions após a transação truncada confirmada.

    Truncar também pode ser anulado aqui o exapmle

     begin Tran delete from Employee select * from Employee Rollback select * from Employee 

    Truncate e Delete in SQL são dois comandos que são usados ​​para remover ou excluir dados da tabela. Embora bastante básico em natureza, ambos os comandos Sql podem criar muitos problemas até que você esteja familiarizado com os detalhes antes de usá-lo. Uma escolha incorreta de comando pode resultar em um processo muito lento ou pode até mesmo explodir o segmento de log, se muitos dados precisarem ser removidos e o segmento de log não for suficiente. É por isso que é essencial saber quando usar o comando truncate and delete no SQL, mas antes de usá-los, você deve estar ciente das Diferenças entre Truncate e Delete, e com base nelas, poderemos descobrir quando DELETE é a melhor opção para remover dados ou TRUNCATE devem ser usados ​​para limpar tabelas.

    Consulte check clique aqui

    Ao emitir uma instrução TRUNCATE TABLE, você está instruindo o SQL Server a excluir todos os registros em uma tabela, sem que nenhum log ou transação ocorra.

    EXCLUIR

    Quando a instrução DELETE é executada sem a cláusula WHERE, ela excluirá todas as linhas. No entanto, quando um novo registro é inserido, o valor de identidade é aumentado de 11 (identidade máxima) para 12. Ele não é redefinido, mas continua aumentando. TRUNCAR

    Quando a instrução TRUNCATE é executada, ela removerá todas as linhas. No entanto, quando um novo registro é inserido, o valor de identidade é aumentado de 11 (que é o valor original). TRUNCATE redefine o valor de identidade para o valor de semente original da tabela.

    SOLTA

    DELETE e TRUNCATE ambos podem ser revertidos quando usados ​​com TRANSACTION.

    Se Transação estiver concluída, significa COMMITED, então não podemos reverter o comando TRUNCATE, mas ainda podemos reverter o comando DELETE dos arquivos LOG, já que DELETE escreve os registros no arquivo de Log, caso seja necessário retroceder no futuro a partir dos arquivos LOG.