Explain the difference between TRUNCATE, DELETE, and DROP commands.
Instruction: Describe the differences and use cases for the TRUNCATE, DELETE, and DROP commands in SQL.
Updated
Example Answer
DELETE removes selected rows and can use a WHERE clause. TRUNCATE empties whole tables while preserving their definitions; DROP removes the table itself. I would confirm the database dialect before discussing rollback or triggers. In PostgreSQL, a TRUNCATE inside an uncommitted transaction can be rolled back, so calling it universally irreversible is wrong. For removing a customer's old records, I would choose a scoped DELETE, verify the affected rows, and consider the transaction and locking cost.
Example: PostgreSQL
BEGIN;
TRUNCATE TABLE staging_import;
ROLLBACK;
The rows are restored by rollback. PostgreSQL TRUNCATE takes an ACCESS EXCLUSIVE lock and fires ON TRUNCATE, rather than ON DELETE, triggers. A transactional DROP TABLE can also be rolled back before commit. Other databases have different DDL transaction rules; verify them before relying on the same recovery behavior. CASCADE can affect dependent tables and deserves an explicit scope check.
Practice follow-up
Why might TRUNCATE be a poor choice while other sessions are reading a table?