Take the 2-minute tour ×

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.

share|improve this question
The FAQ says it's fine to answer your own question. –  Dave Webb Sep 26 '08 at 13:54
Can you clarify in your question Oracle or SQL Server (only tagged Oracle so far) as this seems to have implementation specific differences. –  Guy Sep 29 '08 at 11:54
I took off the Oracle tag. Let it apply to any RDBMS –  David Aldridge Oct 1 '08 at 4:15
All the answers are platform-specific. There is no TRUNCATE command in standard SQL. It is therefore a proprietary feature and means a different thing to each DBMS vendor. –  sqlvogel Jul 8 '11 at 12:54
The answer is very implementation specific, as it must be, since as sqlvogel pointed out, this is a non-standard command (TRUNCATE). Either leave this tagged 'oracle' or let's make it a community-wiki style answer, and put in the consequences for each major RDBMS (Oracle, MS-MSQL, PostgreSQL all implement TRUNCATE...) –  reedstrm Oct 15 '12 at 17:11

24 Answers 24

up vote 61 down vote accepted

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: In some implementations (eg. Oracle) truncate cannot be rolled back.
  • 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.

share|improve this answer
Don't understand your 4th statement: if I say DELETE [] FROM Table; then *all rows in that table will be deleted unless a FK stops it. By the way, I guess this is SQL Server-specific, you can't use TRUNCATE on tables with FKs. –  Joe Pineda Mar 27 '09 at 13:32
Some more comments: I disagree with your 3rd statement, unless it's Oracle-specific. At least with SQL S. either if you DELETE or TRUNCATE you don't recover space (i.e. database files don't shrink on the hard drive) unless you specifically ask for it. –  Joe Pineda Mar 27 '09 at 13:34
4th statement: Sure, but the DELETE command doesn't have to delete all rows because you can apply a predicate to it, whereas the truncate does unless you are truncating a (sub)partition. Obvious, but true –  David Aldridge Mar 27 '09 at 14:34
5TH Statement: you can rollback a TRUNCATE TABLE on sql 2008 r2 –  Eric Labashosky Jul 30 '10 at 13:05
Postgresql can rollback a TRUNCATE and thus also does not autocommit it. –  rfusca Sep 28 '11 at 15:23

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

share|improve this answer
Is this SQLSERVER? –  David Aldridge Sep 26 '08 at 14:11
To clarify, this is for SQL Server if the table has a column defined as IDENTITY. Delete would maintain the last auto-assigned ID, while Truncate resets the counter. –  Codewerks Sep 27 '08 at 14:10
As the question is tagged ORACLE, then this answer is WRONG, therefore downvoted. –  Guy Sep 29 '08 at 11:53
oops, didn't see the oracle tag :) –  mathieu Sep 29 '08 at 15:00
+1 true, and it resets it to 0. If you want it to be 1 instead: DBCC CHECKIDENT (table_name, RESEED, 1) –  JohnB Aug 6 '10 at 16:57

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

share|improve this answer

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

share|improve this answer

"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.

share|improve this answer

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.

share|improve this answer

All good answers, to which I must add:

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

share|improve this answer
Ah, triggers ... that's a good point. I'll add that to the list I made and credit you Polara, if that's OK. –  David Aldridge Sep 26 '08 at 14:30
SQL Server will not let you truncate a table with foreign keys, so your cascading point could be moot, depending on platform. –  Meff Sep 26 '08 at 20:22
@Meff: Good catch. I updated my answer accordingly. –  polara Sep 30 '08 at 21:59
PostgreSQL has a "TRUNCATE Trigger" –  a_horse_with_no_name Feb 1 '12 at 11:04

Can't do DDL over a dblink.

share|improve this answer

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.

share|improve this answer

TRUNCATE is fast, DELETE is slow.

Although, TRUNCATE has no accountability.

share|improve this answer

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.

share|improve this answer
PostgreSQL is similar to this as well. –  Gavin M. Roy Sep 27 '08 at 14:00

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)

         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
share|improve this answer

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

share|improve this answer
yes i tested it –  Ogrish Man Jun 9 '10 at 8:04

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

share|improve this answer

The difference between truncate and delete is listed below:

enter image description here

share|improve this answer

DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

The Syntax of a SQL DELETE statement is:

DELETE FROM table_name [WHERE condition];

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

share|improve this answer


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]


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]

For more details visit


share|improve this answer

It is not that truncate does not log anything in SQL Server. truncate does not log any information but it log the deallocation of data page for the table on which you fired TRUNCATE.

and truncated record can be rollback if we define transaction at beginning and we can recover the truncated record after rollback it. But can not recover truncated records from the transaction log backup after committed truncated transaction.

share|improve this answer

Truncate can also be Rollbacked here the exapmle

begin Tran
delete from  Employee

select * from Employee
select * from Employee
share|improve this answer

If accidentally you removed all the data from table using Delete/Truncate. You can rollback committed transaction. Restore the last backup and run transaction log till the time when Delete/Truncate is about to happen.

The related information below is from a blog post:

While working on database, we are using Delete and Truncate without knowing the differences between them. In this article we will discuss the difference between Delete and Truncate in Sql.


  • Delete is a DML command.
  • Delete statement is executed using a row lock,each row in the table is locked for deletion.
  • We can specify filters in where clause.
  • It deletes specified data if where condition exists.
  • Delete activities a trigger because the operation are logged individually.
  • Slower than Truncate because it Keeps logs


  • Truncate is a DDL command.
  • Truncate table always lock the table and page but not each row.As it removes all the data.
  • Cannot use Where condition.
  • It Removes all the data.
  • Truncate table cannot activate a trigger because the operation does not log individual row deletions.
  • Faster in performance wise, because it doesn't keep any logs.

Note: Delete and Truncate both can be rolled back when used with Transaction. If Transaction is done, means committed then we can not rollback Truncate command, but we can still rollback Delete command from Log files, as delete write records them in Log file in case it is needed to rollback in future from log files.

If you have a Foreign key constraint referring to the table you are trying to truncate, this won't work even if the referring table has no data in it. This is because the foreign key checking is done with DDL rather than DML. This can be got around by temporarily disabling the foreign key constraint(s) to the table.

Delete table is a logged operation. So the deletion of each row gets logged in the transaction log, which makes it slow. Truncate table also deletes all the rows in a table, but it won't log the deletion of each row instead it logs the deallocation of the data pages of the table, which makes it faster.


share|improve this answer
Thank you @AstroCB –  wpzone4u Aug 21 at 12:15
Thank @Lucas: he inlined the content. I just put it in a blockquote to make it apparent that it was from a difference source. –  AstroCB Aug 21 at 20:36
Thank you Lucas & Thank you @AstroCB for your honesty. –  wpzone4u Aug 22 at 19:16

TRUNCATE can be rolled back if wrapped in a transaction.

Please see the two references below and test yourself:-



The TRUNCATE vs. DELETE is one of the infamous questions during SQL interviews. Just make sure you explain it properly to the Interviewer or it might cost you the job. The problem is that not many are aware so most likely they will consider the answer as wrong if you tell them that YES Truncate can be rolled back.

share|improve this answer

Truncate and Delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both Sql commands can create lot of trouble until you are familiar with details before using it. An Incorrect choice of command can result is either very slow process or can even blew up log segment, if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use truncate and delete command in SQL but before using these you should be aware of the Differences between Truncate and Delete, and based upon them, we should be able to find out when DELETE is better option for removing data or TRUNCATE should be used to purge tables.

Refer check click here

share|improve this answer

By issuing a TRUNCATE TABLE statement, you are instructing SQL Server to delete every record within a table, without any logging or transaction processing taking place.

share|improve this answer

One further difference of the two operations is that if the table contains an identity column, the counter for that column is reset 1 (or to the seed value defined for the column) under TRUNCATE. DELETE does not have this affect.

share|improve this answer

Your Answer


