What is the difference between deleting a table and truncating a table command.
This issue was submitted on November 23, 2004
Table deletions include table definitions and associated objects (rules, indexes, approx, triggers, primary keys, and so on). Obviously, once the table is deleted, all rows of data contained in the table are deleted together.
The truncate <table> command only deletes all data rows in the table. The structure of the table and all the indexes still persist until you enter the command to delete the table (as described above). Rules, defaults, constraints that are bound to columns continue to bind, and triggers still work.
Truncating the table command also reclaims the allocation page for all indexes.
Truncating a table's execution speed is the same as the delete command without a WHERE clause, even faster than it does. Delete Deletes one row of data at a time, and each row of deleted data is logged as a transaction, while the TRUNCATE (truncated) table reclaims the entire data page, recording only a small number of log entries. Delete (delete) and truncate (truncation) reclaim the space occupied by the data and the associated indexes. Only the owner of the table can truncate the table.