DELETE and TRUNCATE both remove rows from a table, but they operate at different levels -- DELETE is a row-by-row DML operation that can be filtered and rolled back, while TRUNCATE is a fast DDL-like operation that empties the entire table at once.
Key Points: • DELETE supports a WHERE clause, so you can remove a subset of rows; TRUNCATE always removes every row. • DELETE is logged row by row and can be rolled back within a transaction; TRUNCATE is minimally logged and, in most databases, cannot be rolled back once committed. • TRUNCATE resets auto-increment/identity counters back to their starting value; DELETE does not. • TRUNCATE is generally much faster than DELETE for clearing an entire large table because it deallocates data pages instead of removing rows individually. • DELETE fires row-level triggers; TRUNCATE typically does not.
Example: To remove only cancelled orders you would use DELETE FROM orders WHERE status = 'CANCELLED', but to wipe a staging table completely before a nightly reload you would use TRUNCATE TABLE staging_orders for speed.
Code Example:
DELETE FROM orders WHERE status = 'CANCELLED';
TRUNCATE TABLE staging_orders;Interview Tip: A concise interview answer is:
"DELETE removes rows conditionally, is transaction-safe, and fires triggers, whereas TRUNCATE wipes the entire table in one fast operation, resets identity columns, and is minimally logged. I use DELETE when I need to remove a subset or want rollback safety, and TRUNCATE when I need to quickly empty a whole table, like a staging table before a reload."