Tags

, , , , , , , , , , , , , , , , , , ,

Difference between DELETE and TRUNCATE

DELETE : DELETE table syntax logs the deletes. This makes DELETE a transaction safe. It can be rolled back since it logs the deletes. One draw back is that, since logs the activity is slow.

TRUNCATE : TRUNCATE does not logs any information, but logs the information about de-allocation of tables. This is not transaction safe, since there is no logs. Also this make the operation faster.

DELETE : Removes all records from the table, but it does not rebuild the entire table. So the auto_increment fields will be retaining their values and will not be reset to 0.

TRUNCATE : Removes all records from the table, and rebuilds the entire table. Thus it resets the auto_increment fields to 0.

DELETE : This can have criteria, it fails if foreign key constraints are broken.

TRUNCATE : This does not obey foreign constraints.

DELETE : DELETE works with triggers.

TRUNCATE : TRUNCATE does not works with triggers.

 

DROP table will remove all the data, table structure and integrity constraints. In case of DELETE and TRUNCATE, we will have the table structure intact.

DELETE will remove the data only from table. This does not actually frees much space. TRUNCATE removes all data from the table and frees some space, but is not transaction safe. Also resets the auto_increment fields. DROP will remove everything inside and the entire table and frees the entire space.

Advertisements