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. :-)
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.
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.
answered Jan 17, 2010 at 03:36 PM