Delete vs Truncate vs Drop in SQL
Last Updated on Wednesday 5th Oct 2022
- SQL = Structured Query Language => Structured Query Language
- It is a language used to analyze uncluttered (structured) data. It is the coding language used to analyze the data in the database.
- can delete one record.
- can delete all records at the same time.
- table structure will be in the database even though all records got deleted.
- can roll back after the delete operation.
- delete operation is slower than drop, truncate.
- can not truncate one record.
- can truncate only all records at the same time.
- table structure will be in the database even though all records got truncated.
- can not drop one record.
- can drop only all records at the same time.
- table structure will not be in the database when the table got dropped.
- can not roll back after drop operation.
- drop operation is faster than delete.
DELETE vs. TRUNCATE
DELETE used to remove records using the
TRUNCATE removes all records.
TRUNCATE is not possible when a foreign key references a table.
DELETE vs. DROP
DROP DDL command removes a table from the database.
all named elements of the schema like relations, domains or constraints.
DELETE removes only those tuples which satisfy the
WHERE clause condition.
- If the
WHERE clause is missing, then all tuples present in relation are removed by default.
TRUNCATE vs. DROP
DROP Removes table definition, indexes, data, constraints, triggers.
- Cannot be rolled back (must be recreated)
TRUNCATE removes all data but preserves the table's structure.
- It remains in the memory for further operations, unlike the