Everything You Need to Know About Truncating Tables in SQL

The truncate TABLE command is used to delete all data from an existing table. It removes the data from a table, but not the table itself. The structure of the table and its columns, constraints, indexes, etc. remain intact.

To delete the table definition in addition to its data, use the DROP TABLE statement. Truncate will delete the entire table structure from the database, and you will have to recreate the table if you want to store data. When the TRUNCATE TABLE statement is issued, the database system deletes all rows from the table by deallocating the data pages allocated by the table. In this way, the RDBMS can reduce the resources for logging and the number of locks that need to be acquired.

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. To delete all data from an existing table, use the SQL TRUNCATE TABLE command. You can also use the DROP TABLE command to delete an entire table.

The truncate command removes rows by de-assigning pages. It makes an entry for page deallocation in the transaction log and does not record deletion of each row in the transaction log. The DELETE statement deletes rows one at a time and inserts an entry in the transaction log for each deleted row. Let's explore the difference between the SQL Delete command and the SQL Truncate command in more detail. The MATERIALIZED VIEW LOG clause allows you to specify whether a materialized view record defined in the table should be retained or purged when the table is truncated.

PRESERVE Specify PRESERVE if any materialized view records should be retained when the master table is truncated. WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated. The TRUNCATE TABLE statement in SQL is a data definition language (DDL) operation that labels the extensions of a table for DE mapping (empty for reuse). However, it runs faster and uses fewer system and transaction log resources than DELETE statements. You must truncate the cluster, delete all rows from the table, or delete and recreate the table. In conclusion, TRUNCATE TABLE is used to delete all records from a table quickly and efficiently.

It removes all rows from a table by deallocating pages that are used to store table data and inserts only page deallocations into transaction logs. It is faster than DELETE statements because it does not record deletion of each row in transaction logs.