The truncate command is used to remove all the rows from a table, while the DROP TABLE statement is used to delete the table definition in addition to its data. The TRUNCATE TABLE statement removes all the rows of a table, but the structure of the table and its columns, constraints, indexes, etc. remain intact. The DELETE command can be used to delete all rows or just a subset of rows, and can be applied to tables and tables within a cluster.
Truncate only applies to tables or the entire cluster. TRUNCATE removes all rows from a table quickly and doesn't use as much undo space as DELETE. It also adds a table-level lock when truncating. TRUNCATE cannot be undone and no trigger will be activated. It also demaps pages and creates an entry for de-mapping pages in the transaction log. When using the TRUNCATE TABLE statement with the supertable in a hierarchy, the ONLY keyword allows you to truncate only the supertable or to truncate the supertable and all of its subtables.
The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated. To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partition function).Another difference between TRUNCATE and DELETE is that if the table contains an identity column, the counter for that column is reset to 1 (or to the initial value defined for the column) in TRUNCATE. TRUNCATE transactions can be undone in database engines such as SQL Server and PostgreSQL, but not in MySQL and Oracle. In conclusion, TRUNCATE is faster than DELETE as it does not generate rollback information. It also resets identity columns while DELETE does not.
However, DELETE allows you to specify a WHERE clause while TRUNCATE does not.
Leave Reply