Ads by ProfitSence
Close

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.

Delete

  • 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.

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.

Drop

  • 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 WHERE clause.
  • 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.
  • Removes 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 DROP table.