Differences between TRUNCATE and DELETE for a table ?

Please could someone explain the differences between TRUNCATE and DELETE commands on a database table with an example to me? Thank you in advance.

more ▼

asked Jan 14, 2010 at 11:37 AM in Default

avatar image

771 73 75 79

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There are quite a few differences.

First, TRUNCATE does not examine the data rows at all and, as Hillbilly Toad points out, gets no more than a quick note in the logs. It basically just puts all the table's data pages back into the free pool. This does mean it's very fast, but it's a one-way trip to no data.

Second, TRUNCATE is regarded as DDL. This means it does a COMMIT (in fact, it commits before and after its action). Any pending statements are committed.

Third, any triggers and most other checks are ignored. However, if there are foreign keys to the table you intend to truncate, you must disable them first.

Fourth, a hash cluster can't be truncated. Since I don't yet know what a hash cluster is, I don't know if this matters much or not. :-)

Truncate on Oracle.com

FYI - Nobody asked but TRUNCATE can be rolled back on SQL Server, if it's part of a transaction. We found this handy in batch jobs, particularly DTS packages, it was easy to write a practically bulletproof reload of a reference table sourced in another system.

Also, I'm told that there's a TRUNCATE on Teradata but that an unrestricted DELETE is faster. I find it hard to believe but I heard it from a very reliable source. I've never had a chance to test it myself. I don't know if it can be rolled back or not.

more ▼

answered Jan 17, 2010 at 09:46 PM

avatar image

1.5k 9 11 14

Since somebody who actually participates in this site has mentioned them, I've now answered the question about clusters here: http://www.oracleoverflow.com/questions/618/ - there's no real analogue in SQL Server, and to be honest, they're of limited value in most real-life scenarios I've encountered, especially as IOTs (clustered index equivalent) have become better supported in the past 10 years.

Jan 20, 2010 at 06:44 AM Andrew Mobbs
(comments are locked)
10|1200 characters needed characters left

TRUNCATE is going to be much faster as the changes are logged in the redo/undo. You can't rollback a TRUNCATE.

If you issue a DROP then you could roll it back or use DMBS_LOGMINER to undo the change after the fact.

TRUNCATE's great in a development/test environment...not so much in PRODUCTION.

more ▼

answered Jan 17, 2010 at 03:36 PM

avatar image

1k 2 4

Would a DROP and re-CREATE have any effect on dependency information?

Jan 20, 2010 at 01:36 AM KillerDBA
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 14, 2010 at 11:37 AM

Seen: 3965 times

Last Updated: Jan 15, 2010 at 01:17 PM

Copyright 2018 Redgate Software. Privacy Policy