Difference between truncate, delete and drop table

Difference between truncate, delete and drop table

In mysql, as well as other database system, there are many ways to get rid of data or table itself. So what are the differences?

DML vs DDL

In order to know the difference better, you have to know SQL commands are basically divided into:

1. DML (Data Manipulation Language)
2. DDL (Data Definition Language)
3. DCL (Data Control Language)
4. TCL(Transaction Control Language)

DML deal with data manipulation (CRUD), which is probably what everyone uses most commonly. DML usually needs to commit explicitly to take effect. DML statements include:

1. SELECT: retrieve data from database
2. INSERT: insert data into database
3. UPDATE: update existing data in database
4. DELETE: delete existing data in database
5. LOCK TABLE: concurrency control for table in database

DDL deal with data definition – schema changes, build and modify the structure of tables and other objects. This means it deals with how data resides in the database. DDL statement has a implicit commit, which means it will take effect immediately. DDL statements include:

1. CREATE: create database or objects including table, index, triggers, procedures, etc
2. DROP: delete objects from database
3. TRUNCATE: delete all records from the table as well as the space allocated to the records
4. ALTER: alter the structure of existing table or objects
5. RENAME: rename an object

DCL deals with access control for database systems. DCL statements include:

1. GRANT: grant user access privileges to database
2. REVOKE: take the privileges back

TCL deals with transaction in database. TCL statements include:

1. COMMIT: commit the current transaction
2. ROLLBACK: rollback transaction in case of any exception or error
3. SET TRANSACTION: specify attributes for transaction

DELETE vs TRUNCATE

In short, TRUNCATE is faster than DELETE.

1. DELETE is meant for data manipulation, it deletes record row by row, hence requires a row level lock.
2. DELETE keeps all the logs for deleting rows so that we can recover from the deletion in case anything happens. It supports rollback, which is the main reason it’s slower than truncate.
3. DELETE will trigger the on delete triggers.
4. DELETE won’t free space

As opposed:

1. TRUNCATE is not meant for data manipulation, it remove all data in table, hence requires a table level lock.
2. TRUNCATE does not keep logs for deleting rows. Hence, truncate is not available for rollback based on logs*.
3. TRUNCATE won’t trigger on delete triggers
4. TRUNCATE will free space, as well as re-render the auto increment indices.

* There is always exception :-D. If you use TRUNCATE command within a transaction, it can be rolled back.

TRUNCATE vs DROP

DROP goes even further. It will removes the table from the database completely. This includes removing all data and associated objects, such as indexes and constraints. If you dropped a table, not only would you remove all the data, but the structure as well.

Similar with TRUNCATE, you can only row back if you execute DROP within a transaction.