Posted by The difference between DELETE and TRUNCATE command is the most common database interview question. We use the DELETE and TRUNCATE commands to delete the data from the table in the database.
The Delete command deletes data from a table based on the condition we specify with a WHERE clause. Truncate removes all the rows from a database, leaving no data in the table when we run the truncate command.
Delete:
Delete is a DML(Data Manipulation Language) command to delete the row from the table.
Syntax: DELETE FROM table_name;
It will delete all data in a table.
Syntax: DELETE FROM table_name WHERE condition;
It will delete a subset of rows in a table.
Example: DELETE FROM users WHERE age = 25;
It will delete the rows from the user’s table where the age equals 25.
You can use the following operators in the DELETE commands.
=, >, >=, =, IN, and BETWEEN, etc.
Truncate:
Truncate is a DDL(Data Definition Language) command to delete all the rows from the table and it free the space containing the table.
Syntax: TRUNCATE TABLE table_name;
Example: TRUNCATE TABLE users;
Difference between DELETE and TRUNCATE
| DELETE | TRUNCATE |
|---|---|
| DELETE command removes one or multiple rows from a table depending on the conditions. | The TRUNCATE command removes the complete rows from the table But, the table structure, including columns, constraints, and indexes, remains. |
| DELETE does not reset the table’s identity. | TRUNCATE resets the table’s identity. |
| It keeps transaction logs for every deleted record. | It doesn’t keep transaction logs for every deleted data. |
| DELETE works slowly because it maintains the logs. | TRUNCATE works fast because it does not maintain the log. |
| DELETE allows us to roll back the deleted data. | TRUNCATE does not allow us to roll back the deleted data. |
| DETETE is DDL command. | TRUNCATE is a DML command. |