Detailed explanation
DELETE, TRUNCATE and DROP differ mainly in what they remove: selected data, all data, or the table itself. DELETE and TRUNCATE preserve the table definition, so applications can continue using its columns and constraints. DROP removes the table object, including its data, indexes and associated constraints. Interviewers expect you to explain these differences and recognise that rollback, identity handling and logging depend on the database system.
DELETE is a DML command that removes rows matching a WHERE condition. For example, DELETE FROM orders WHERE status = 'cancelled'; removes only cancelled orders; without WHERE, it removes every row. The database processes affected rows, maintains indexes, enforces constraints and invokes applicable DELETE triggers. DELETE generally involves more row-level logging and work than TRUNCATE for a large table. In a transactional database or storage engine, it can be rolled back before commit; an already committed or autocommitted deletion cannot simply be undone using ROLLBACK. DELETE normally does not reset identity or auto-increment counters.
TRUNCATE removes all rows and does not accept a WHERE clause. It typically deallocates or resets storage structures rather than processing individual row deletions, which often makes it faster for clearing large tables. It is not unlogged: databases record the changes needed for their recovery and transaction models. It does not fire DELETE triggers, although PostgreSQL supports separate TRUNCATE triggers. Identity behaviour varies: SQL Server resets identity, whereas PostgreSQL requires RESTART IDENTITY to reset associated sequences. Foreign-key references can prevent truncation or require database-specific handling.
DROP TABLE orders; removes the table definition, so subsequent queries against that table fail unless it is recreated. Dependencies may block the operation or require explicit handling. Although TRUNCATE and DROP are generally classified as DDL, that label alone does not determine rollback behaviour. SQL Server and PostgreSQL can roll back ordinary table truncation and dropping inside transactions; MySQL and Oracle generally implicitly commit these operations. Choose DELETE for selective removal, TRUNCATE for clearing a retained table, and DROP when the table is no longer needed.
Key points to remember
- DELETE supports WHERE; omitting it deletes all rows while retaining the table.
- TRUNCATE clears the entire table, retains its structure and typically uses less logging than DELETE.
- DROP removes the table definition as well as its data.
- DELETE invokes applicable DELETE triggers; TRUNCATE does not, but database-specific TRUNCATE triggers may exist.
- Rollback, identity resets and foreign-key restrictions must be explained for the specific database.
Common mistakes candidates make
- Claiming that TRUNCATE and DROP can never be rolled back, regardless of the database.
- Saying TRUNCATE is completely unlogged or always resets identity counters.
- Assuming DELETE without WHERE removes the table definition, or that TRUNCATE supports filtered deletion.
Likely follow-up questions
Can I roll back DELETE, TRUNCATE and DROP?
DELETE can normally be rolled back before commit when transactional storage is used. SQL Server and PostgreSQL also support rollback of ordinary TRUNCATE and DROP TABLE operations within transactions, while MySQL and Oracle generally implicitly commit those DDL operations.
Why is TRUNCATE usually faster than DELETE without WHERE?
TRUNCATE typically clears storage through allocation or metadata changes instead of performing individual row deletions and their associated work. It still performs logging and takes locks, so it is neither unlogged nor guaranteed to be instantaneous.
Can I truncate a table referenced by a foreign key?
Many databases restrict truncation of a table referenced by another table's foreign key, even when the referencing table is empty. The exact options vary: PostgreSQL, for example, supports truncating related tables together or using CASCADE, which can remove additional data.
Interview tip: Start with “selected rows, all rows, table itself,” then qualify rollback and identity behaviour using the database named by the interviewer.