What's the difference between TRUNCATE and DELETE in SQL

Question

I wrote up an answer to this question by mistake in response to a question about the difference between DROP and TRUNCATE, but I thought that it's a shame not to share so I'll post my own answer to my own question ... is that even ethical? :)

Edit: If your answer is platform specific can you please indicate that.

Answer 1

Here's my complete list. Some are probably Oracle-specific but others will be generally applicable. Some are obvious, but still worth stating I think.

Statement type: Delete is DML, Truncate is DDL

Commit: Delete has no autocommit, a truncate is autocommited (actually, there are two commits involved I believe)

Space reclamation: Delete does not recover space, Truncate recovers space (unless you use the REUSE STORAGE clause)

Row scope: Delete can remove only some rows. Truncate removes all rows except where used in a partitioning context.

Object types: Delete can be applied to tables and tables inside a cluser. Truncate applies only to tables or the entire cluster

Data Object ID's: Delete does not affect the data object id, but truncate assigns a new data object id unless there has never been an insert against the table (even a single insert that is rolled back will cause a new data object id to be assigned).

Rollback: Delete can be rolled back. Truncate cannot.

Flashback: Flashback works across deletes, but a truncate prevents flashback operations to before the operation.

Grants: Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.

Redo/Undo: Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.

Indexes: A truncate operation renders unusable indexes usable again. Delete does not.

Foreign Keys: A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys

Locking: Truncate requires an exclusive table lock, delete requires a shared table lock.

Triggers: DML triggers do not fire on a truncate. (thanks Polara)

Um ... let me think ... I'll add more if i think of them. Let me know if I missed any and I'll add them and credit you.

Answer 2

With SQL Server, if there is a PK with auto increment, truncate will reset the counter

Answer 3

In short, truncate doesn't log anything (so is much faster but can't be undone) whereas delete is logged (and can be part of a larger transaction, will rollback etc). If you have data that you don't want in a table in dev it is normally better to truncate as you don't run the risk of filling up the transaction log

Answer 4

The biggest difference is that truncate is non logged operation while delete is.

Simply it means that in case of a database crash , you cannot recover the data operated upon by truncate but with delete you can.

More details here

Answer 5

"Truncate doesn't log anything" is correct. I'd go further:

Truncate is not executed in the context of a transaction.

The speed advantage of truncate over delete should be obvious. That advantage ranges from trivial to enormous, depending on your situation.

However, I've seen truncate unintentionally break referential integrity, and violate other constraints. The power that you gain by modifying data outside a transaction has to be balanced against the responsibility that you inherit when you walk the tightrope without a net.

Answer 6

A big reason it is handy, is when you need to refresh the data in a multi-million row table, but don't want to rebuild it. "Delete *" would take forever, whereas the perfomance impact of Truncate would be negligible.

Answer 7

Short answer:

You can rollback a DELETE but you can't rollback a TRUNCATE.

Answer 8

All good answers, to which I must add:

Since TRUNCATE TABLE is not a DML command, the delete triggers do not run.

Answer 9

Can't do DDL over a dblink.

Answer 10

Truncate is faster then deleting as it deletes whole pages instead of rows in SQL Server.

Answer 11

I'd comment on matthieu's post, but I don't have the rep yet...

In MySQL, the auto increment counter gets reset with truncate, but not with delete.

Answer 12

TRUNCATE is fast, DELETE is slow.

Although, TRUNCATE has no accountability.

Answer 13

Yes, DELETE is slower, TRUNCATE is faster. Why?

DELETE must read the records, check constraints, update the block, update indexes, and generate redo/undo. All of that takes time.

TRUNCATE simply adjusts a pointer in the database for the table (the High Water Mark) and poof! the data is gone.

This is Oracle specific, AFAIK.

Answer 14

A small correction to the original answer - delete also generates significant amounts of redo (as undo is itself protected by redo). This can be seen from autotrace output:

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

Answer 15

In SQL Server 2005 I believe that you can rollback a truncate

Answer 16

TRUNCATE is the DDL statement where as DELETE is a DML statement. Below are the differences between the two

1. As TRUNCATE is a DDL(data definition language) statement it does not require a commit to make the changes permanent. And this is the reason why rows deleted by truncate could not be rollbacked. On the other hand DELETE is a DML(data manipulation language) statement hence requires explicit commit to make its effect permanent.
2. Truncate always removes all the rows from a table, leaving the table empty and the table structure intact whereas delete may removes conditionally if the where clause is used.
3. The rows deleted by TRUNCATE TABLE statement cannot be restored and you can not specify the where clause in the TRUNCATE statement.
4. TRUNCATE statements did not fire triggers as opposed of on delete trigger on DELETE statement

Here is the very good link relevant to the topic

http://forums.oracle.com/forums/thread.jspa?threadID=636943