Easy5 minDatabase Fundamentals

DELETE vs TRUNCATE

SQL Indexing

Why This Is Asked

This question tests the candidate’s understanding of DML (Data Manipulation Language) vs DDL (Data Definition Language) and the performance/transactional implications of each.

Key Concepts

  • DELETE is DML; TRUNCATE is DDL.
  • Logging: DELETE logs every row removal; TRUNCATE logs only page deallocations.
  • Filters: DELETE supports a WHERE clause; TRUNCATE does not.
  • Identity: TRUNCATE resets the IDENTITY seed; DELETE does not.
  • Triggers: DELETE fires AFTER DELETE triggers; TRUNCATE does not.

Answers by Technology

+ Add Variant
SQLImprove this answer ✏️

Expected Answer

DELETE and TRUNCATE both remove data from a table, but they operate at different levels of the database engine:

  1. Operation Type: DELETE is a DML operation that removes rows one by one. TRUNCATE is a DDL operation that deallocates the data pages used by the table.
  2. Performance: TRUNCATE is significantly faster for large tables because it doesn’t log the deletion of each individual row.
  3. Space Reclamation: TRUNCATE immediately releases the space to the database; DELETE often leaves “holes” in pages that are only reclaimed after a vacuum or shrink operation.
  4. Constraints: You cannot TRUNCATE a table that is referenced by a Foreign Key constraint (unless the FK is disabled or dropped).

Why It Matters

Using DELETE on a table with 100 million rows can bloat the transaction log, cause lock escalation (blocking other users), and take a very long time. TRUNCATE is the “nuclear option” for quickly clearing a staging or log table.

SQL Example

-- DELETE: Selective and logged
DELETE FROM Orders 
WHERE OrderDate < '2023-01-01';

-- TRUNCATE: All-or-nothing and fast
TRUNCATE TABLE Staging_Orders;

Common Mistakes

  • Assuming TRUNCATE isn’t transactional: In many databases (SQL Server, Postgres), TRUNCATE can be rolled back if it’s inside a transaction block. In others (MySQL), it causes an implicit commit.
  • Forgetting Identity reset: If you use DELETE to clear a table, the next INSERT will continue from the last ID. If you use TRUNCATE, it starts back at 1 (or the seed).

Performance Notes

TRUNCATE uses fewer system and transaction log resources. DELETE requires one log entry for every row deleted, while TRUNCATE only logs the deallocation of the data pages.

PostgreSQL Notes

In PostgreSQL, TRUNCATE is fully transactional and safe to use within a BEGIN/COMMIT block. It also supports the CASCADE option to truncate all tables that have foreign-key references to the table.

SQL Server Notes

TRUNCATE is allowed in a transaction and can be rolled back. However, it is blocked if there are any enabled Foreign Keys pointing to the table, even if those tables are empty.

Follow-up Questions

  • What happens to the Transaction Log during a massive DELETE? (Answer: It grows rapidly because it must record every row’s previous state to allow for a rollback).
  • Does TRUNCATE fire triggers? (Answer: No, triggers are row-level or statement-level DML events. Since TRUNCATE is DDL and doesn’t process rows, triggers are bypassed).

References