What's the difference between TRUNCATE
and DELETE
in SQL?
If your answer is platform specific, please indicate that.
The delete statement is used to remove single or multiple records from an existing table depending on the specified condition. The truncate command removes the complete data from an existing table but not the table itself. It preserves the table structure or schema.
Truncate removes all records and doesn't fire triggers. Truncate is faster compared to delete as it makes less use of the transaction log. Truncate is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.
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.
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.
Delete is DML, Truncate is DDL (What is DDL and DML?)
Variable by vendor
SQL*Server
Truncate can be rolled back.
PostgreSQL
Truncate can be rolled back.
Oracle
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.
Delete does not recover space, Truncate recovers space
Oracle
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.
Delete can be used to remove all rows or only a subset of rows. Truncate removes all rows.
Oracle
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.
Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)
Oracle
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 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
Variable
Oracle
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.
Oracle
A truncate operation renders unusable indexes usable again. Delete does not.
A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys.
Oracle
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.
Oracle
DDL triggers are available.
Oracle
Truncate cannot be issued over a database link.
SQL*Server
Truncate resets the sequence for IDENTITY column types, delete does not.
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, last_name INTO emp_first_name, emp_last_name;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With