TRUNCATE

Hook: Interviewers like TRUNCATE because it looks tiny, but it tests big ideas: speed, locks, rollback, and foreign keys.

Question: What does TRUNCATE do in SQL?

Answer: TRUNCATE empties a whole table at once. It is usually much faster than DELETE because the database does not visit every row one by one. You cannot filter it with WHERE, and details like identity reset or rollback depend on the database engine.

Interview-Ready Answer: I use TRUNCATE when I need to clear every row from a table quickly. Compared with DELETE, it usually does less row-by-row work, so it is faster on large tables and creates less logging. The two things I always check are foreign keys and engine-specific behavior, because TRUNCATE can fail on referenced tables and rollback or identity reset is not identical across databases.

What it is

TRUNCATE TABLE is the fast way to remove all rows from a table. Think of it as a table reset, not a search-and-delete. Most interviewers treat it like a DDL-style operation because it changes the table's storage and metadata rather than editing rows one by one. Metadata means the database's stored information about the table, such as its definition, counters, and internal storage state.

How it works under the hood

1. The SQL parser checks that the table exists and that you have permission to change it.
2. The engine takes a strong table lock, often a metadata lock or exclusive lock, so other sessions cannot safely read or write the table while the operation runs.
3. Instead of walking every row, the storage layer discards the table's data pages or marks them free for reuse. This is why the work is usually close to constant time with respect to row count.
4. The engine updates table bookkeeping. That may include resetting an identity or auto-increment counter if you ask for it, and handling dependent objects such as foreign keys.
5. The statement finishes as one unit. In databases that support transactional TRUNCATE, a rollback can undo it; in others, it may cause an implicit commit.

Why it is fast

With DELETE, the engine typically visits each matching row, removes it, updates indexes, and writes more log records. That makes the cost roughly O(n) for n rows. TRUNCATE is usually close to O(1) with respect to row count because it changes storage metadata instead of deleting rows one by one. On a table with 10 million rows, TRUNCATE is often milliseconds to a few hundred milliseconds, while DELETE can take seconds or minutes and produce much larger logs.

TRUNCATE vs DELETE vs DROP

Key takeaway: DELETE is for selective removal, TRUNCATE is for emptying a whole table, and DROP is for removing the table itself.

Important version differences

• PostgreSQL: TRUNCATE is transactional, supports RESTART IDENTITY, and can use CASCADE for related tables.
• MySQL/InnoDB: TRUNCATE is often implemented like a drop-and-recreate operation and usually causes an implicit commit.
• SQL Server: TRUNCATE TABLE resets identity by default, but it is blocked by foreign key references unless those references are removed first.

Edge cases to remember

• You cannot add WHERE to TRUNCATE.
• Foreign keys may block it unless you truncate dependent tables too or use a supported CASCADE option.
• It often does not fire row-level DELETE triggers.
• Freeing table space from the database file is engine-specific; the OS file may or may not shrink right away.

Real-world story: Imagine a checkout service that keeps a temporary cart_snapshot_staging table for nightly batch jobs. A small cleanup script uses TRUNCATE to clear the table before loading fresh data. One day a teammate adds a new audit table with a foreign key pointing to that staging table, and the cleanup job starts failing with an error like “cannot truncate a table referenced in a foreign key constraint.” The pipeline does not clear the staging data, the batch job retries, logs fill up, and the next morning dashboards show stale cart counts. The bug is not the speed of TRUNCATE; it is assuming every table can be truncated in isolation.

Follow-up & Tricky Questions:

• Can TRUNCATE be rolled back? In PostgreSQL, yes, if it runs inside a transaction. In some other databases, it causes an implicit commit, so the answer depends on the engine.
• Does TRUNCATE fire DELETE triggers? Usually not row-level DELETE triggers, because rows are not deleted one by one. Some databases have special statement-level trigger behavior, so you must know the dialect.
• Does TRUNCATE reset identity or auto-increment? Often yes, or it has an option to do so, but that is database-specific. In PostgreSQL you can say RESTART IDENTITY; in other systems the syntax is different.
• What happens with foreign keys? A referenced table may not be truncatable by itself. You may need to truncate child tables too, use CASCADE where supported, or choose DELETE instead.
• When should I prefer TRUNCATE over DELETE? Use it when you need the entire table empty, you do not need a row filter, and you want the faster metadata-level reset.
• Is TRUNCATE always faster? Usually on big tables, but not always worth it if you only need a few rows removed, or if the lock and dependency checks outweigh the savings.
• Can I use WHERE with TRUNCATE? No. That is the simplest gotcha: TRUNCATE is all-or-nothing for the table.
• Does it always free disk space immediately? Not necessarily. The database may release storage for reuse, but whether the physical file shrinks is engine- and storage-specific.

Common Mistakes:

• Thinking TRUNCATE is just a faster DELETE. Correction: it is faster because it changes storage metadata and usually skips row-by-row work.
• Forgetting that WHERE is not allowed. Correction: if you need to remove only some rows, use DELETE.
• Assuming rollback always works. Correction: transactional behavior depends on the database; PostgreSQL allows it, but some engines auto-commit.
• Ignoring foreign keys and triggers. Correction: check dependent tables and engine-specific trigger rules before truncating production data.

Memory Hook: DELETE is a broom that sweeps one piece at a time; TRUNCATE is dumping out the whole trash bag and replacing the bag liner.

Cheat Sheet:

• TRUNCATE removes all rows from a table.
• No WHERE, no row-by-row delete.
• Usually much faster and less logged than DELETE.
• May reset identity/auto-increment counters.
• Can be blocked by foreign keys or require CASCADE.
• Rollback and trigger behavior depend on the database.

Practice Tasks:

• Create a small table, insert 5 rows, then compare DELETE FROM table and TRUNCATE TABLE table.
• Add an identity column, truncate with and without identity reset, and observe the next inserted id.
• Create a parent-child foreign key pair and see when truncation fails versus when truncating both tables succeeds.