Delete Vs Truncate in SQL
Comparison of DELETE and TRUNCATE TABLE Commands in SQL
As SQL developers, we need to understand the difference between Delete and Truncate because both remove data from the table.
What is DELETE Command in SQL?
The DELETE command in SQL is used to remove one or more rows from a table based on specified conditions. It allows you to selectively delete specific rows or delete all rows in a table. DELETE comes under DML (Data Manipulation Language) operations.
Here's the syntax of the DELETE command with the where condition:
DELETE FROM table_one WHERE condition;
The WHERE clause (optional) is used to specify the conditions that must be met for a row to be deleted. If no WHERE clause is specified, all rows in the table will be deleted.
Here's the syntax of the DELETE command without the where condition:
DELETE FROM table_name;
This removes all rows from the specified table.
Deleting rows using DELETE can trigger associated actions such as triggers or cascading deletes, depending on the database configuration.
The DELETE command will have a log and can be undone within a transaction using a rollback.
What is TRUNCATE Command in SQL?
The TRUNCATE TABLE statement is used in SQL to quickly and efficiently remove all rows from a table while keeping the table structure intact. TRUNCATE comes under DDL (Data Definition Language) operations.
Here's the syntax of the TRUNCATE command:
TRUNCATE TABLE table_one;
With the TRUNCATE command we can't use the where condition. It removes all rows from the specified table but retains the table structure, indexes, and constraints.
Unlike the DELETE command, which deletes rows one by one and generates individual log entries for each deletion, the TRUNCATE command is more efficient for removing all data from a table.
TRUNCATE resets any auto-increment sequences or identity columns associated with the table.
The key difference between DELETE and TRUNCATE Commands.
| Delete | Truncate | |
| Operation type | DML (Data Manipulation Language) | DDL (Data Definition Language) |
| Speed | DELETEis slow compared to truncate because it deletes each row individually. | TRUNCATE is faster compared to the delete command. It achieves this by deallocating the data pages used by the table. |
| Transactional and log | DELETE, command will have a log and can be undone within a transaction using a rollback. | TRUNCATE TABLE is usually not recorded in the transaction log and cannot be rolled back. |
| WHERE Clause | We can use the WHERE clause with DELETE command. | We can't use a WHERE clause with TRUNCATE. |
| Triggers | DELETE removes rows based on specified conditions and can have triggers or other associated actions. | TRUNCATE does not activate the triggers. |
| Resets identity | DELETE command does not reset the values of identity columns or sequence generators. If you delete rows from a table with an auto-incrementing identity column, the next inserted row will continue from where it left off. | If the table has an auto-incrementing identity column or a sequence generator, TRUNCATE TABLE resets the value of the column or sequence back to its initial value. |
Conclusion
Consider using DELETE when you need to remove specific rows based on conditions and require more control over the deletion process. Use TRUNCATE TABLE when you want to remove all rows from a table, reset identity columns, and release storage space efficiently.
While using any of these commands be careful, as they permanently affect the data in your tables. Always double-check the conditions. It's also a good practice to take backups and test your queries in a safe environment before executing them on production data.