What's the difference between TRUNCATE and DELETE in SQL

What's the difference between TRUNCATE and DELETE in SQL

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 by David Aldridge for What's the difference between TRUNCATE and DELETE in SQL

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

---

Commit and Rollback

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.

---

Space reclamation

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.

---

Row scope

Delete can remove only some 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.

---

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

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 (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

---

Privileges

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.

---

Redo/Undo

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

---

Indexes

Oracle

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

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.

---

Triggers

DML triggers do not fire on a truncate.

Oracle

DDL triggers are available.

---

Remote Execution

Oracle

Truncate cannot be issued over a database link.

---

Identity Columns

SQL*Server

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

Answer by mathieu for What's the difference between TRUNCATE and DELETE in SQL

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

Answer by Oskar for What's the difference between TRUNCATE and DELETE in SQL

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 by Learning for What's the difference between TRUNCATE and DELETE in SQL

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 by Walter Mitty for What's the difference between TRUNCATE and DELETE in SQL

"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 by Jordan Ogren for What's the difference between TRUNCATE and DELETE in SQL

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 by polara for What's the difference between TRUNCATE and DELETE in SQL

All good answers, to which I must add:

Since TRUNCATE TABLE is a DDL (Data Defination Language), not a DML (Data Manipulation Langauge) command, the Delete Triggers do not run.

Answer by joel garry for What's the difference between TRUNCATE and DELETE in SQL

Can't do DDL over a dblink.

Answer by nathan for What's the difference between TRUNCATE and DELETE in SQL

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 by databyss for What's the difference between TRUNCATE and DELETE in SQL

TRUNCATE is fast, DELETE is slow.

Although, TRUNCATE has no accountability.

Answer by DCookie for What's the difference between TRUNCATE and DELETE in SQL

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 by CaptainPicard for What's the difference between TRUNCATE and DELETE in SQL

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 by Xander for What's the difference between TRUNCATE and DELETE in SQL

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

Answer by Sachin Chourasiya for What's the difference between TRUNCATE and DELETE in SQL

TRUNCATE is the DDL statement whereas 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 remove 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 does not fire triggers as opposed of on delete trigger on DELETE statement

Here is the very good link relevant to the topic.

Answer by Bhaumik Patel for What's the difference between TRUNCATE and DELETE in SQL

The difference between truncate and delete is listed below:

Answer by Bhushan Patil for What's the difference between TRUNCATE and DELETE in SQL

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.

Answer by user27332 for What's the difference between TRUNCATE and DELETE in SQL

DELETE

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

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

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

Answer by user2587360 for What's the difference between TRUNCATE and DELETE in SQL

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.

Answer by Vinay Pandit for What's the difference between TRUNCATE and DELETE in SQL

Truncate can also be Rollbacked here the exapmle

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

Answer by wpzone4u for What's the difference between TRUNCATE and DELETE in SQL

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:

• 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

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

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