Difference Between DELETE and TRUNCATE in SQL
DELETE and TRUNCATE commands can both be used to erase table data. TRUNCATE is a DDL command, while DELETE is a DML command. TRUNCATE can be used to erase all of the table's data while keeping the table's integrity. This article describes the difference between the DELETE and TRUNCATE commands, which is commonly used interchangeably yet are completely different.
DELETE vs TRUNCATE in SQL
DELETE command is used for removing some or all of the records from a table. In contrast, TRUNCATE statement deletes complete rows from a table. Further, the difference between DELETE and TRUNCATE commands are given below.
DELETE:
- It's a data manipulation language (DML) command.
- It deletes records from a table that is no longer needed in the database.
- It specifies that we have a database backup before running this operation, as this query will not be able to restore deleted records.
- Database backups enable to restore data at any time in the future.
- A WHERE clause can be included in the DELETE command.
- When the WHERE clause is used with the DELETE command, only those rows(tuples) that satisfy the criterion are removed or deleted; otherwise, all tuples(rows) are removed by default.
TRUNCATE:
- It is a statement is a DDL (data definition language) command.
- It removes all of the data from a table while leaving the structure intact.
- WHERE clause cannot be utilized with this operation.
- The log is not kept while doing this action, we cannot revert the erased data after running this command.
- The transaction log for each destroyed data page is not recorded when using the TRUNCATE command.
- The TRUNCATE command is faster than the DELETE command.
- After invoking the TRUNCATE command, data cannot be reversed.
Related Topics:
Comments
write a comment