TRUNCATE TABLE statement is faster and more efficient than the
DELETE statement in SQL databases.
It is because
TRUNCATE TABLE is a DDL command, unlike
DELETE it does not delete records one by one and logs them to the log table, but drops the whole table and recreates the structure.
This is why we cannot use the
WHERE clause with the
TRUNCATE TABLE command.
If we have to delete some records which match certain conditions, then we should use
Another reason for faster execution of truncate is that it does not fire triggers which avoids the activation and execution time of triggers.
The delete statement without the
WHERE clause is equivalent to the truncate statement, however
TRUNCATE deletes records faster than the delete command.
Syntax of Truncate
The syntax to delete all the records of a table in one go is:
TRUNCATE TABLE table_name;
To delete entire records of multiple tables:
TRUNCATE TABLE table1, table2, table3, ...;
The table which we want to delete may have foreign keys reference, so in that case, we can mention
CASCADE to also delete the referencing data:
TRUNCATE TABLE table_name CASCADE;
Because the TRUNCATE is a DDL command, it is auto committed. We do not need to explicitly commit after truncating a table.