The TRUNCATE TABLE statement is faster and more efficient than the DELETE statement in SQL databases.
This 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 DELETE.
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.
