Difference between DELETE and TRUNCATE

DELETE is a DML command where as TRUNCATE is DDL command. You can’t rollback after using TRUNCATE and trigger will not be fired, also it is faster than DELETE.
But using DELETE: we can rollback the transaction and need to commit for permanent change, it is slower than TRUNCATE.

TRUNCATE resets auto increment value to one, where as DELETE will not reset the value and next record get next highest value after insertion of new records.

Structure of DELETE and TRUNCATE is as follows:

DELETE FROM table_name
TRUNCATE FROM table_name

Both delete records from table.

Posted in MySQL | Tagged , , , , , , , , , | 1 Comment

One Response to Difference between DELETE and TRUNCATE

  1. pkadmin says:

    DDL: Data Definition Language.
    DML: Data Manipulation Language.

    CREATE, DROP, TRUNCATE etc are DDL command
    SELECT, INSERT, DELETE etc are DML command.