DELETE and TRUNCATE are both SQL commands used in MariaDB (and other SQL databases) to remove records from a table. However, there are several differences:
1. DELETE is a DML (Data Manipulation Language) command whereas TRUNCATE is a DDL (Data Definition Language) command.
1. DELETE allows you to delete specific records from a table based on the WHERE clause. But TRUNCATE will remove all records from a table. You can’t use a WHERE clause with TRUNCATE.
1. DELETE statement has to track every row that has been deleted in the transaction log, which can slow down large deletion operations. On the other hand, TRUNCATE command doesn’t keep track of individual row deletions – it just quickly removes the whole table.
1. DELETE activates a trigger because the operation is logged. However, TRUNCATE will not activate a trigger because the operation does not log individual row deletions.
1. TRUNCATE resets the IDENTITY value (auto\_increment) to its initial value (usually 1), whereas DELETE leaves the IDENTITY value unchanged.
1. TRUNCATE command cannot be used if the table is referenced by a Foreign Key or if it participates in an INDEX VIEW. In this case, DELETE command can be used.
1. Delete operation can be rolled back, but Truncate operation cannot be rolled back.
1. TRUNCATE can be faster than DELETE, because it uses fewer system and transaction log resources.
