The DELETE statement and the TRUNCATE command are two of the most commonly used commands in SQL. While they both delete data from a table, there are some key differences between them. The DELETE statement deletes the rows one at a time and records one entry in the transaction log for each deleted row.
TRUNCATE TABLE
, on the other hand, removes the data by demapping the data pages used to store the table data and records only page deallocations in the transaction log.The DELETE command is slower than the TRUNC command. Everyone should know that DELETE is a DML command and TRUNCATE is a DDL command. DELETE deletes records one by one and creates an entry for each and every deletion in the transaction log, while TRUNCATE demaps pages and creates an entry for de-mapping pages in the transaction log. It's faster than the delete command.
Both Delete and Truncate commands can be used to delete data from the table. Delete is a DML command, whereas truncate is a DDL command. Truncate can be used to remove all data from the table without maintaining the integrity of the table. Alternatively, the delete statement can be used to delete specific data.
With the delete command, we can't bypass integrity compliance mechanisms. The DELETE statement analyzes each row before deleting it. Therefore, it is slower compared to the TRUNCATE command. If we want to delete all the records in a table, it is preferable to use TRUNCATE instead of DELETE, since the first one is faster than the second. The main difference between them is that the delete statement deletes data without restoring the identity of a table, while the truncate command restores the identity of a particular table.
If you have a foreign key constraint that references the table you are trying to truncate, it will not work even if the reference table contains no data. In short, truncating doesn't log anything (so it's much faster but can't be undone), whereas deleting is logged (and can be part of a larger transaction, it will get undone, etc.). In PostgreSQL, you need the TRUNC privilege; in SQL Server, the minimum permission is ALTER table; in MySQL, you need the DROP privilege. TRUNCATE always deletes all rows from a table, leaving the table empty and the table structure intact, whereas DELETE can conditionally delete if the where clause is used. The DELETE statement can have a WHERE clause to delete specific records, whereas the TRUNCATE statement requires none and deletes the entire table. The truncate statement is a data definition language or DDL command that is used to remove entire data from the table without deleting the structure of the table.
TRUNCATE cannot be used in the table referenced by a FOREIGN KEY constraint, unless a table has a foreign key that references itself. TRUNCATE can also be undone as long as it is included in a TRANSACTION block and the session is not closed. Another difference between these two operations is that if the table contains an identity column, that column's counter is reset to 1 (or to its initial value defined for that column) when using TRUNC. The Truncate command is used to reinitialize tables; it is a DDL command that removes all rows from a given table. It needs to be tested if TRUNCATE resets IDENTITY seeds and if it follows cascading deletions for foreign key RI.
