Drop, Truncate, and Delete : The Difference

If you wanted to get rid of all the data in a table, then you could you either of the three. However there are certain situations which using a different one of these is the best route to go.

Drop

A drop table will drop the table from the database, dropping all the keys, triggers, etc… You will want to use this option if you have a temporary table or when performing maintenance on an old database.

DROP TABLE Cars

Truncate

A truncate table will erase all the data from a table. It is quicker than a delete since it does not have all the undo information, however is irreversible since it does not save the truncated information. This is the best solution for a logging table that you for sure do not need any of the existing rows.

TRUNCATE TABLE Cars

Delete

A delete will delete either all, or specific rows based on the where parameter information. Since it is held in the undo until committed, it is reversible. Delete is also the only one of the three which will fire trigger, drop and truncate will not. This is best used for when you need to delete only certain rows of table, do not need the highest possible speed, and require the availability of undo.

DELETE FROM CARS