What is the difference between delete drop and truncate in SQL

TL;DR

Delete is a DML command that removes rows and supports where clauses, while keeping the table structure. Truncate is a DDL command that deletes all rows in one operation, locks the table, and cannot be rolled back after commit. Drop removes the entire table schema and data.

Transcript

Hello friends Welcome you to Gate Smashers In this video I am going to explain Difference between Delete, Drop and Truncate Which is an important topic of SQL And this is important From point of view of competitive exam Or your college or university level exams point of view And we talk from the placement point of view Over there also it is very im... Read More

Key Insights

• Delete is a DML command that removes rows from a table.
• Drop removes the entire table structure and data from the database.
• Truncate deletes all rows but preserves the table structure for future use.
• Delete can include a where clause to delete specific rows.
• Truncate locks the table and executes a bulk delete in one operation.
• Drop is risky and typically requires DBA authorization because it cannot be rolled back.
• Delete supports rollback before commit due to logging.
• Truncate is faster than delete because it avoids per-row logging and constraints.
• More videos from this channel:
  • How to write nested SQL queries for max salary
  • What is software maintenance: perfective, preventive, adaptive
  • What is a batch operating system and how it works
• More from this channel: Gate Smashers

Questions & Answers

Q: What is the difference between delete and truncate in SQL?

Delete and truncate both remove data from a table, but delete is a DML command that deletes specific rows or all rows with an optional where clause, and it logs each deletion, allowing rollback before commit. Truncate is a DDL command that removes all rows in one operation, locks the table, and cannot be rolled back after commit.

Q: Can you roll back a delete operation after it runs?

Yes, if the delete operation has not been committed, you can roll back to restore the deleted rows because delete logs the changes. This logging enables recovery from the operation before finalizing the transaction. Once committed, the changes are permanent and require other recovery mechanisms.

Q: What happens to the table when you drop it?

Dropping a table removes the entire table structure and its data from the database. After a drop, there is no table schema or data to describe, and typically rollback does not apply to a dropped table in standard operations. It is a destructive action that eliminates the object entirely.

Q: When would you use truncate instead of delete?

You would use truncate when you want to quickly remove all rows from a table and you do not need to preserve individual row deletions or perform conditional removals. Truncate is faster because it performs a bulk delete in one operation and locks the table during the process.

Q: Is there a way to delete specific rows with delete?

Yes, you can delete specific rows by using a where clause, such as delete from table where condition. This allows selective removal without affecting other rows. It can be slower than truncate since it processes rows individually and logs each deletion for potential rollback.

Q: What language category do delete and truncate belong to?

Delete belongs to DML, the Data Manipulation Language, which handles data changes within existing structures. Truncate and drop belong to DDL, the Data Definition Language, which handles definitions and removals of database objects, such as schemas, tables, and their structures.

Q: Can you describe the rollback behavior of truncate compared to delete?

Delete supports rollback before commit because it logs the changes, allowing recovery of deleted rows. Truncate does not log individual row deletions and, once committed, cannot be rolled back in typical usage. It operates as a single, irreversible operation that clears all data from the table.

Q: What is the effect of a where clause on delete versus truncate?

A where clause on delete narrows the deletion to specific rows, leaving the rest of the table intact and preserving its structure. Truncate does not support a where clause; it removes all rows from the table as a single bulk operation, affecting every row without exceptions.

Summary & Key Takeaways

• Delete removes rows and can be rolled back before commit, preserving table structure, and allows conditional deletion with where. Truncate removes all rows at once, locks the table, and does not log individual row deletions, making rollback difficult. Drop removes the table entirely, including its structure, data, and metadata.

• Truncate is faster than delete because it operates on the whole table and avoids per-row logging, but cannot selectively delete with a where clause. Delete allows selective removal via conditions and can be rolled back if not yet committed. Drop eliminates the table completely with no recovery in normal rollback.

• Delete, truncate, and drop belong to different language subsets: DML for delete, DDL for truncate and drop. Delete preserves data of other rows when a where clause is used, whereas truncate clears all rows. Drop only leaves nothing left of the table and its description in the database.