In SQL, the truncate TABLE statement is a data definition language (DDL) operation that marks extensions of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table and generally ignores a number of integrity enforcement mechanisms. TRUNCATE TABLE removes all rows from a table, but the structure of the table and its columns, constraints, indexes, etc. remain.
To delete the table definition in addition to its data, use the DROP TABLE statement. The SQL TRUNCATE TABLE command is used to delete complete data from an existing table. It performs the same function as a DELETE statement without a WHERE clause. SQL Truncate is a data definition language (DDL) command.
It deletes all rows in a table by unallocating the pages that are used to store table data. The TRUNCATE TABLE command removes data from a table, but not the table itself. On a system with a large set of InnoDB buffers and innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations can cause a temporary drop in system performance due to an LRU scan that occurs when you delete adaptive hash index entries from an InnoDB table. Remapping TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 prevents troublesome scanning of LRU.
Truncating a table can be faster and doesn't affect any of the table's indexes, triggers, or dependencies. At a high level, you might consider the truncate command to be similar to a Delete command without a Where clause. As long as the table definition is valid, the table can be recreated as an empty table with TRUNCATE TABLE, even if the data files or indexes have been corrupted. Microsoft SQL Server has the ability to delete or truncate tables that have more than 128 extensions without maintaining simultaneous locks on all extensions required for deletion.
Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows or a sequence of DROP TABLE and CREATE TABLE statements. To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partitioning function). In conclusion, truncating is an efficient way to delete all records from an existing table in SQL. It performs the same function as a DELETE statement without a WHERE clause and is classified as a DDL statement rather than a DML statement.
It does not record the deletion of each row in the transaction log and makes an entry for page deallocation in the transaction log. Truncating is faster than deleting and doesn't affect any of the table's indexes, triggers, or dependencies.
