Why is Truncate Used in SQL?

The SQL TRUNCATE TABLE statement is used to delete all records in a table. It performs the same function as a DELETE statement without a WHERE clause, deleting all rows in a table or specified partitions of a table, without recording deletions of individual rows. TRUNCATE TABLE is faster and uses fewer system log and transaction resources than the DELETE statement. Transact-SQL syntax conventions allow for the truncation of specified partitions of a partitioned table. The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.

The TRUNCATE TABLE command removes data from a table, but not the table itself; it is classified as a data definition language (DDL) command. TRUNCATE TABLE is faster than the DELETE command as it places a table and page lock to delete all records. It also requires less space in the transaction log than the DELETE command. The SQL TRUNCATE TABLE command deallocates pages used to store the table data and makes an entry for page deallocation in the transaction log. The Delete command is useful for deleting all or some rows from a table specified by a WHERE clause. However, the TRUNCATE command deletes all rows from a table without using a WHERE clause.

To have TRUNCATE TABLE create the tablespace in its current location, add the directory to the innodb_directories configuration before running TRUNCATE TABLE. When used with partitioned tables, TRUNCATE TABLE preserves the partition; that is, data files and indexes are deleted and recreated, while partition definitions are not affected. However, TRUNCATE TABLE operations on tables that use a DDL-compliant atomic storage engine are fully committed or rolled back if the server is stopped during its operation. At a high level, you might consider the truncate command similar to a DELETE command without a WHERE clause. Remapping TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 prevents problematic scanning of LRU. TRUNCATE TABLE can be used with performance schema summary tables, but the effect is to reset summary columns to 0 or NULL, not delete rows. 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. TRUNCATE TABLE is treated for binary logging and replication purposes as DDL rather than as DML, and is always recorded as an instruction.

There are many ways to delete data in SQL, including the DELETE, TRUNCATE TABLE, and DROP TABLE commands.