This very common question in interviews. Both remove records from the table, so what is the difference.
- Delete :
Syntax : Delete from TableName [where condition];
Deletes record from table . But doesn’t reset the auto_increment
DELETE operations are transaction-safe and logged, which means DELETE can be rolled back.
DELETE will fire any ON DELETE triggers.DELETE will fail if foreign key constraints are broken. - Truncate :
Syntax : truncate table TableName;
Deletes all record from table. Reset the auto_increment.
TRUNCATE cannot be done inside a transaction and can’t be rolled back.
Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE. Truncate the Trigger is not fired.There are difference in Delete and Truncate:
1. Delete use for delete row by row but truncate will delete the entire table.
2. In Truncate rollback not possible.
3. Value of Auto Increment will reset from starting after use of Truncate not in Delete.
4. Truncate is a DDL(Data Definition Language) command and Delete is a DML(Data Manipulation Language) command.
5. When Delete the Particular row the Corresponding Delete Trigger(if exists) Fire.In Case of Truncate the Trigger is not fired.But I found issue with both command, they will take time to execute when data is more than 2GB. So , I will prefer to Drop table & again create it.
Before dropping table, export the structure of table using PHPMYADMIN or database administrative tools in sql file, so we can import the sql file, which will creates table structure.
sadhak99
Leave a Reply