Introduction
DELETE and TRUNCATE are the SQL commands, both of them are used to delete data from a table in the database. But there are differences between them. Depending on the requirement, we can choose which one to use to delete the rows from a table. For example, you might want to delete only specific rows and not all rows from a table or you may want the data to be restored if deleted accidentally. Sometimes, you may want to delete all the rows from a table quickly without a need to restore them. So, as a developer, you need to know when to use DELETE and when to use TRUNCATE.
What does DELETE do?
DELETE is a DML (Data Manipulation Language) statement. It can delete a specific row or some of the rows or even all rows from a table. You can use WHERE condition with DELETE statement to choose which row or rows to be deleted. Examples of DELETE statements:
To delete all rows from the table Employees, use:
DELETE FROM EMPLOYEES;
To delete a row from the Employees table for the employee with ID 200, use:
DELETE FROM EMPLOYEES WHERE EMP_ID = 200;
The database keeps a log of the changes in the database in the transaction log for DELETE statement. So, when you delete data using DELETE statement, we can restore the deleted data.
What does TRUNCATE do?
TRUNCATE is a DDL (Data Definition Language) statement. TRUNCATE also deletes rows from a table. However, you cannot use a filtering of data to delete a specific row or some of the rows. TRUNCATE deletes all data from the table. So, you cannot use the WHERE condition in the TRUNCATE statement. Example:
TRUNCATE TABLE EMPLOYEES;
In this case, all the rows from the Employees table will be deleted. Also, if data is deleted using the TRUNCATE statement, it cannot be restored. Because the database does not write the transaction logs for the deleted data using TRUNCATE. So, we should use the TRUNCATE statement carefully.
Differences between DELETE and TRUNCATE
We can summarize the differences between DELETE and TRUNCATE commands in the below table.
| DELETE | TRUNCATE |
|---|---|
| 1. DELETE command deletes row-by-row data and it is slower. | 1. TRUNCATE command deletes data by deallocating data pages in the database and it is faster than DELETE. |
| 2. DELETE is a DML command. | 2. TRUNCATE is a DDL command. |
| 3. Deleted data is written in the Database Log file, so the data can be restored after DELETE statement. | 3. Deleted data is NOT written in the Database Log file, only the page deallocation details are written in the transaction log. So, data can NOT be restored after TRUNCATE statement. |
| 4. You can delete selected rows using DELETE statement. | 4. TRUNCATE deletes all rows from the table, you cannot filter data to be deleted. |
| 5. DELETE statement can activate a trigger because it works row-by-row basis. | 5. TRUNCATE cannot raise a trigger as it does not delete row-by-row data. |
| 6. DELETE retains the auto-increment values in the table. If you have 100 rows in a table and you deleted all rows using DELETE statement, then for the next insert auto-increment will start from 101. | 6. TRUNCATE resets the auto-increment as it drops and recreates the table. In this case, after using TRUNCATE, auto-increment will start from 1. |
Conclusion
I have given the differences between DELETE and TRUNCATE in MySQL. Many of these are valid for other databases as well, like Oracle and SQL Server. Most of the time you will be using DELETE statement. Deleting all rows using a DELETE statement can be slower. If you do not need to rollback or restore the data, using TRUNCATE will be faster. But remember that you cannot roll back the data with the TRUNCATE command. So, you need to be careful while using the TRUNCATE command.