DELETE, TRUNCATE and DROP table commands in SQL

1. DELETE only deletes the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. It does not free the space containing the table.

TRUNCATE is used to delete all the rows from the table and free the space containing the table. It does not delete the table structure.

DROP is used to remove an object from the database. All the indexes and privileges also get removed. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back. When a table is dropped all the references to the table become invalid.

2. Syntax:

DELETE FROM table_name [WHERE condition];
To delete all rows:
DELETE FROM table_name;

TRUNCATE TABLE table_name;

DROP TABLE table_name;

3. DELETE does not reset the identity seed but TRUNCATE does reset it. DROP deletes every thing including the identity seed, of course!

4. After performing a DELETE operation, COMMIT or ROLLBACK is needed to make the changes permanent or to undo them.
In case of TRUNCATE, there is no roll back. Same is the case for DROP - no roll back.
In other words, DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

During DELETE, all the data get copied into the Rollback Tablespace first and then the delete operation gets performed. That is why ROLLBACK can get back the data even after deleting a table. But during TRUNCATE,it removes data directly without copying it anywhere. That is why TRUNCATE is faster but it has the disadvantage that you cannot retrieve the data.

5. A trigger doesn’t get fired in case of TRUNCATE or DROP whereas Triggers get fired in DELETE command.

6. DELETE and TRUNCATE both are logged operation. But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists. There is no logging in case of DROP.

7. If you have to delete all the rows of a table you should perform TRUNCATE command with DROP STORAGE option. Truncate drops the storage held by this table which can then be used by this table again or some other table. This is useful for improved performance.