Easy5 minDatabase Fundamentals
UpdatedAug 1, 2026
Edit

DELETE vs TRUNCATE

CONCEPTS:SQL Indexing

Question Variations

  • "What are the main differences between `DELETE` and `TRUNCATE` in SQL?"
  • "Why is `TRUNCATE` generally faster than `DELETE` for large tables?"
  • "Can you roll back a `TRUNCATE` operation? Does it depend on the database engine?"
  • "How does `TRUNCATE` handle identity columns differently than `DELETE`?"

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.

Question Variations

  • “What are the main differences between DELETE and TRUNCATE in SQL?”
  • “Why is TRUNCATE generally faster than DELETE for large tables?”
  • “Can you roll back a TRUNCATE operation? Does it depend on the database engine?”
  • “How does TRUNCATE handle identity columns differently than DELETE?”

Answers by Technology

+ Add Variant

Expected Answer (PostgreSQL 16)

DELETE is a DML operation; TRUNCATE is a DDL operation. Both are fully transactional in PostgreSQL.

  1. Logging: DELETE logs rows; TRUNCATE logs page deallocations.
  2. FKs: TRUNCATE requires CASCADE if FKs exist.

Why It Matters

Using DELETE on millions of rows can cause transaction log bloat and performance lag. TRUNCATE is the efficient way to clear tables, and PostgreSQL’s ability to roll back a TRUNCATE makes it safer than in many other RDBMS.

SQL Example

-- Selective Delete
DELETE FROM audit_logs WHERE created_at < '2023-01-01';

-- Full Table Clear (Transactional)
BEGIN;
TRUNCATE TABLE staging_data RESTART IDENTITY;
COMMIT;

Common Mistakes

  • Assuming TRUNCATE isn’t transactional: It is in Postgres!
  • Identity Reset: TRUNCATE does not reset sequences by default in Postgres unless RESTART IDENTITY is specified.

Follow-up Questions

  • Does TRUNCATE fire triggers? (Answer: Only statement-level truncate triggers).
  • Can you filter a TRUNCATE? (Answer: No).

Expected Answer (MySQL 8.4)

  1. DELETE: A DML operation. Rows are deleted one by one. It is transactional and can be rolled back (in InnoDB).
  2. TRUNCATE: A DDL operation. It drops and re-creates the table.
    • Implicit Commit: In MySQL, TRUNCATE causes an implicit commit. It cannot be rolled back.

Why It Matters

The “Implicit Commit” behavior of TRUNCATE in MySQL is a critical safety difference compared to PostgreSQL or SQL Server. If you run TRUNCATE inside a transaction block, the transaction is immediately committed and cannot be undone.

SQL Example

-- Safe, transactional delete
DELETE FROM session_logs WHERE expiry < NOW();

-- Nuclear option (cannot be rolled back)
TRUNCATE TABLE staging_data;

Common Mistakes

  • Assuming rollback works: TRUNCATE is DDL in MySQL and ends any active transaction.
  • Trigger behavior: TRUNCATE does not fire ON DELETE triggers.

Follow-up Questions

  • What happens to the AUTO_INCREMENT value? (Answer: TRUNCATE resets it to the start).
  • Is TRUNCATE faster? (Answer: Yes, because it skips the row-by-row deletion and logging).

Expected Answer (SQL Server 2022)

DELETE is a logged DML operation; TRUNCATE is a minimally logged DDL operation.

  1. Identity: TRUNCATE resets identity seeds.
  2. Foreign Keys: TRUNCATE is forbidden if the table is referenced by an FK.

Why It Matters

Choosing between DELETE and TRUNCATE impacts performance and the transaction log. In SQL Server, TRUNCATE is a powerful tool for clearing staging tables but has strict requirements regarding foreign key relationships.

SQL Example

-- Selective Delete
DELETE FROM Orders WHERE Status = 'Cancelled';

-- Fast Table Clear
TRUNCATE TABLE StagingOrders;

Common Mistakes

  • Forgetting Identity reset: TRUNCATE will make the next insert start at 1, which might break logic expecting a continuous ID sequence.
  • Log growth: A massive DELETE can fill the transaction log disk.

Follow-up Questions

  • Is TRUNCATE transactional? (Answer: Yes, in SQL Server it can be rolled back).
  • Can you truncate a table with a filter? (Answer: No).

Expected Answer (MongoDB 7.0/8.0)

In MongoDB, the equivalent concepts are:

  1. db.collection.deleteMany({}): Similar to DELETE. It removes all documents from a collection. It is slower for large sets because it must delete each document and update all associated indexes.
  2. db.collection.drop(): Similar to TRUNCATE. It removes the entire collection, including all indexes. This is the fastest way to clear data.

Why It Matters

In MongoDB, deleting millions of documents with deleteMany creates a massive amount of work for the wiredTiger storage engine and the oplog (for replication). drop() is an atomic metadata operation that is nearly instantaneous.

Example

// Slow (Delete row-by-row)
db.logs.deleteMany({ status: "old" });

// Fast (Nuclear option)
db.temp_data.drop();

Common Mistakes

  • Forgetting Indexes: drop() removes all indexes. If you re-create the collection, you must re-create the indexes manually. deleteMany({}) leaves the index definitions intact.
  • Oplog Bloat: A large deleteMany can fill the oplog, potentially causing replication lag for secondary nodes.

Follow-up Questions

  • Is there an equivalent to a filtered delete? (Answer: Yes, deleteMany({ field: value })).
  • Does drop() affect the database? (Answer: No, only the specific collection).

Expected Answer (Cassandra 5.0)

Cassandra handles deletions fundamentally differently because of its LSM-tree architecture.

  1. DELETE: Does not actually remove data immediately. It writes a Tombstone (a marker that the data is deleted). The data is physically removed later during Compaction.
  2. TRUNCATE: A cluster-wide DDL operation that removes all data from all nodes for a specific table.

Why It Matters

In Cassandra, “deleting too much” can actually make your database slower. If you have many tombstones, the database must read through them to find “live” data. This is known as “Tombstone Pressure.” TRUNCATE avoids this by clearing the data files (SSTables) entirely.

CQL Example

-- Creates a tombstone
DELETE FROM users WHERE user_id = 123;

-- Clears all nodes (Immediate)
TRUNCATE users;

Common Mistakes

  • Using DELETE for bulk clearing: This creates millions of tombstones and can lead to TombstoneOverwhelmingException.
  • Assuming immediate disk space reclamation: Deletes only free space after the tombstone expires (gc_grace_seconds) and compaction runs.

Follow-up Questions

  • What is gc_grace_seconds? (Answer: The time a tombstone is kept to ensure it replicates to all nodes before being deleted).
  • Can you roll back a TRUNCATE? (Answer: No).