SQL Essentials: Truncate vs Delete vs Drop

DELETE, TRUNCATE, and DROP answer different questions

Use DELETE to remove matching rows, TRUNCATE to empty a table, and DROP TABLE to remove the table object. Transaction, trigger, identity, and dependency behavior depends on the database engine. In particular, “TRUNCATE can never be rolled back” is false for PostgreSQL and SQL Server.

This guide distinguishes PostgreSQL 16, MySQL 8.4 with InnoDB, and SQL Server. The worked script is PostgreSQL-only and uses a session-local temporary table. Run it in a disposable learning database, not against application tables.

Start with the required outcome

TRUNCATE: empty a table with engine-specific rules

TRUNCATE does not accept a row predicate

The core form is TRUNCATE TABLE table_name. It targets all rows of the specified table, with partition and inheritance details depending on the engine. It is not a substitute for DELETE ... WHERE ... when some rows must remain.

Check rollback, triggers, and identity explicitly

Read the engine's own reference before using a dialect-specific option: PostgreSQL TRUNCATE, MySQL TRUNCATE TABLE, and SQL Server TRUNCATE TABLE. This table is not a complete list of partition, replication, temporal-table, or permission restrictions.

Faster does not mean harmless

TRUNCATE often avoids the per-row deletion work of a large DELETE. It still performs database work and may take disruptive locks. PostgreSQL takes an ACCESS EXCLUSIVE table lock. SQL Server logs page deallocations. Neither behavior supports the blanket claim that truncation is “unlogged.” Measure the operation in the actual engine and workload.

Do not bypass dependency checks casually

An empty referencing table may still have a foreign-key constraint that prevents truncation. Removing that constraint or adding a cascading option changes the operation's meaning. Review the full affected object set before proceeding; do not treat a constraint error as a reason to disable integrity checks.

DELETE: remove rows selected by a predicate

An omitted WHERE means all rows

DELETE leaves the table definition in place. A zero-row result is valid when nothing matches. Trigger and foreign-key actions can affect what actually happens, so inspect those rules as well as the visible predicate. PostgreSQL's DELETE reference documents its RETURNING extension for inspecting deleted values.

Preview the intended population

Write the condition as a SELECT first, check keys and counts, then use the same condition in the deletion. A preview alone does not freeze concurrent data. For an actual change, use the application's approved transaction and concurrency controls and check the affected-row result before committing.

Commit state matters as much as command name

A rollback can undo supported changes only while their transaction is still open. If a client auto-commits a statement, a later ROLLBACK will not undo that committed transaction. MySQL rollback support also depends on the operation and storage engine; the InnoDB TRUNCATE rule above differs from ordinary transactional row deletion.

Work on temporary data while learning

The next script compares the three operations inside savepoints. It rolls back each operation and finally rolls back the entire fixture. The temporary table is explicitly addressed through PostgreSQL's pg_temp schema, so these statements do not accidentally target a similarly named persistent table.

BEGIN;
CREATE TEMP TABLE command_demo (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL
) ON COMMIT DROP;
INSERT INTO pg_temp.command_demo(status)
VALUES ('keep'), ('remove'), ('keep');

SAVEPOINT before_delete;
DELETE FROM pg_temp.command_demo WHERE status = 'remove'
RETURNING id, status;
SELECT COUNT(*) AS rows_after_delete FROM pg_temp.command_demo;
ROLLBACK TO SAVEPOINT before_delete;

SAVEPOINT before_truncate;
TRUNCATE TABLE pg_temp.command_demo RESTART IDENTITY;
SELECT COUNT(*) AS rows_after_truncate FROM pg_temp.command_demo;
ROLLBACK TO SAVEPOINT before_truncate;
SELECT COUNT(*) AS rows_after_truncate_rollback
FROM pg_temp.command_demo;

SAVEPOINT before_drop;
DROP TABLE pg_temp.command_demo;
SELECT to_regclass('pg_temp.command_demo') IS NULL AS table_is_absent;
ROLLBACK TO SAVEPOINT before_drop;
SELECT COUNT(*) AS rows_after_drop_rollback FROM pg_temp.command_demo;
ROLLBACK;

DELETE RETURNING:              id=2, status=remove
rows_after_delete:             2
rows_after_truncate:           0
rows_after_truncate_rollback:  3
table_is_absent:               true
rows_after_drop_rollback:      3

The result demonstrates PostgreSQL behavior in an uncommitted transaction. It is not a recovery method for already committed changes and must not be transplanted unchanged into MySQL. The final rollback removes the fixture itself.

DROP TABLE: remove the object

The table no longer exists afterward

A successful committed drop removes the table definition and data; indexes and constraints owned by that table are also removed. It is not an empty table waiting for the next insert. PostgreSQL's DROP TABLE reference describes its dependency handling.

Dependencies need a separate review

Views, foreign keys, jobs, application queries, and data pipelines may depend on the table. Database dependency checks do not discover every reference in external application code. A cascading drop can remove dependent objects and should be reviewed as its own change.

Drop an object only when it is no longer needed

Examples include a disposable staging object after its consumers finish or a table intentionally removed by a reviewed migration. For a retained table that simply needs fewer rows, use a row-removal operation with the required semantics.

Recovery is an engine-specific process

There is no general SQL command RESTORE TABLE ... FROM backup. Recovery may involve a tested backup restore, point-in-time recovery into a separate instance, or a product-specific feature. A rollback is possible only when that engine supports it and the relevant transaction has not committed. Plan and test recovery before a destructive production change.

Compare behavior rather than slogans

DDL and DML labels do not determine rollback

DELETE is normally classified as data manipulation, while TRUNCATE and DROP are commonly classified as data definition. That terminology alone does not tell you whether an engine can roll back a specific operation.

Evaluate the complete cost

Include lock duration, replication, triggers, log growth, index maintenance, and downstream effects in the decision. “TRUNCATE is always faster” omits both restrictions and operational costs. A selective indexed deletion can also do less work than a full-table operation because it solves a different task.

Logging and recoverability are different properties

An operation can log structural changes rather than individual row removals and still support transaction rollback. Conversely, an atomic operation can commit in a way that a later user rollback cannot reverse. MySQL's atomic DDL crash guarantees do not make TRUNCATE rollbackable in an open user transaction.

Match the requirement precisely

For expired rows, use a defined retention predicate. For a reloadable staging table, evaluate a full clear and its dependency rules. For retiring a table, use a reviewed schema migration. Preserve evidence of the selected IDs or counts and verify the resulting state.

Give a dialect-qualified interview answer

Start with scope and table existence

DELETE can target rows; TRUNCATE clears the table; DROP removes the object. Then ask which database engine is in scope before discussing rollback, counters, or triggers.

Can TRUNCATE be rolled back?

In PostgreSQL and SQL Server, yes, within an appropriate uncommitted transaction. MySQL 8.4 TRUNCATE causes an implicit commit and cannot be undone that way. Do not generalize SQL Server behavior to Azure Synapse Analytics.

What if an audit trigger must capture each deleted row?

Do not assume TRUNCATE invokes DELETE triggers. Choose an operation and audit design that meet that requirement, then verify it in the target engine.

Which claims should I avoid?

Avoid “TRUNCATE is unlogged,” “all DDL is irreversible,” “TRUNCATE always resets every sequence,” and “a rollback fixes any accidental delete.” Each hides engine or transaction details.

Check the engine, scope, and recovery boundary

The right command satisfies the intended row and object outcome while respecting dependencies, transaction state, and audit requirements. Use small disposable fixtures to prove unfamiliar behavior before handling real data.

FAQ

Can TRUNCATE use WHERE?

No. Use DELETE with a predicate when only selected rows should be removed.

Does DELETE remove the table definition?

No. Even an unqualified DELETE leaves an empty table when all rows are removed.

Is a rollback a substitute for a backup?

No. It applies only to supported changes in an uncommitted transaction. Committed data loss requires a separate recovery path.