Sql – What’s the difference between TRUNCATE and DELETE in SQL


What's the difference between TRUNCATE and DELETE in SQL?

If your answer is platform specific, please indicate that.

Best Solution

Here's a list of differences. I've highlighted Oracle-specific features, and hopefully the community can add in other vendors' specific difference also. Differences that are common to most vendors can go directly below the headings, with differences highlighted below.

General Overview

If you want to quickly delete all of the rows from a table, and you're really sure that you want to do it, and you do not have foreign keys against the tables, then a TRUNCATE is probably going to be faster than a DELETE.

Various system-specific issues have to be considered, as detailed below.

Statement type

Delete is DML, Truncate is DDL (What is DDL and DML?)

Commit and Rollback

Variable by vendor


Truncate can be rolled back.


Truncate can be rolled back.


Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway.

However, see Flashback below.

Space reclamation

Delete does not recover space, Truncate recovers space


If you use the REUSE STORAGE clause then the data segments are not de-allocated, which can be marginally more efficient if the table is to be reloaded with data. The high water mark is reset.

Row scope

Delete can be used to remove all rows or only a subset of rows. Truncate removes all rows.


When a table is partitioned, the individual partitions can be truncated in isolation, thus a partial removal of all the table's data is possible.

Object types

Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)

Data Object Identity


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 since its creation Even a single insert that is rolled back will cause a new data object id to be assigned upon truncation.

Flashback (Oracle)

Flashback works across deletes, but a truncate prevents flashback to states prior to the operation.

However, from 11gR2 the FLASHBACK ARCHIVE feature allows this, except in Express Edition

Use of FLASHBACK in Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638




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


Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.



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.

Table Locking


Truncate requires an exclusive table lock, delete requires a shared table lock. Hence disabling table locks is a way of preventing truncate operations on a table.


DML triggers do not fire on a truncate.


DDL triggers are available.

Remote Execution


Truncate cannot be issued over a database link.

Identity Columns


Truncate resets the sequence for IDENTITY column types, delete does not.

Result set

In most implementations, a DELETE statement can return to the client the rows that were deleted.

e.g. in an Oracle PL/SQL subprogram you could:

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