DELETE vs TRUNCATE vs DROP: Differences, Rollback and When to Use Each

A tester wants to clear a staging table before a fresh test run. Should it be DELETE, TRUNCATE or DROP? The wrong choice can mean a slow job, a missing table, or, in production, a very bad afternoon.

We ran the DELETE and DROP examples below in SQLite 3.50, and the outputs come from that run. SQLite has no TRUNCATE command, so the TRUNCATE notes are based on the official PostgreSQL, MySQL and SQL Server documentation rather than our own test.

The short answer

• Need to remove some rows? Use DELETE ... WHERE.
• Need to empty a table but keep it? Use TRUNCATE, where it is supported.
• Need the table gone for good? Use DROP TABLE.

DELETE: remove rows

DELETE is a data change (DML) command. It removes rows one at a time, fires row-level triggers and can be filtered. We set up a small table with an audit trigger:

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

Only the matching row went, and the trigger recorded it. Running DELETE FROM logs without a WHERE clause removed the other three rows, and the audit table then held four entries, one per deleted row.

DELETE does not reset auto-increment counters. After emptying the table, our next insert received id 5, not 1. In SQLite this is because of AUTOINCREMENT. MySQL InnoDB and SQL Server identity columns also continue from where they were after a DELETE.

TRUNCATE: empty the table

TRUNCATE TABLE logs; removes every row at once. It cannot take a WHERE clause. Instead of deleting rows one by one, the database typically releases the table’s data pages, which is why it is much faster on large tables.

When we ran it in SQLite, we got a syntax error, because SQLite does not have the command:

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

In SQLite, use DELETE FROM logs; with no WHERE clause. SQLite’s documentation describes a “truncate optimization” that makes this fast when the table has no triggers. Our table had a trigger, so the delete ran row by row and fired it for each row.

In databases that support TRUNCATE, by their documentation:

• It normally does not fire row-level DELETE triggers.
• It usually resets identity or auto-increment counters, for example in MySQL and SQL Server. In PostgreSQL you add RESTART IDENTITY.
• It needs stronger permissions than DELETE in most systems.

DROP: remove the table itself

DROP TABLE is a schema (DDL) command. It removes the data, the table definition, its indexes, triggers and constraints. Anything that refers to the table, such as views or foreign keys, is affected too.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS is handy in setup scripts that may run more than once. Views on a dropped table can break, as we showed in SQL views explained.

Comparison table

Can you roll back?

This is where a lot of articles oversimplify. The honest answer is that it depends on the database.

DELETE can be rolled back inside a transaction everywhere. We tested it:

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP also rolled back in SQLite, which supports transactional DDL:

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

For TRUNCATE and DROP in other databases, according to their documentation:

So “TRUNCATE can never be rolled back” is wrong for PostgreSQL and SQL Server, and “it can always be rolled back” is wrong for MySQL. Outside a transaction, none of them can be undone without a backup.

Why TRUNCATE is fast

DELETE logs each removed row so the change can be undone and replicated, fires triggers and checks constraints row by row. On a table with millions of rows, that creates a lot of work and log volume. TRUNCATE generally deallocates the storage in one operation, logging far less. To remove a large subset rather than everything, delete in batches so you avoid long locks. We cover locking and query cost in SQL query optimization.

Foreign keys and permissions

• Foreign keys. SQL Server refuses to TRUNCATE a table referenced by a foreign key. PostgreSQL requires you to truncate the referencing tables too, or use CASCADE. A DELETE checks each row against the constraints instead.
• Permissions. DELETE needs delete rights. TRUNCATE often needs ALTER or a dedicated TRUNCATE right, and DROP needs ownership or ALTER on the schema.
• Replication and audit. If other systems depend on row-level change logs or triggers, TRUNCATE may skip them. Check with whoever owns those systems first.

Which one to use

For test data, TRUNCATE is popular because it resets IDs, which makes tests repeatable. Wrapping each test in a transaction that is rolled back is another option, explained in SQL transactions and ACID.

Safety checklist

1. Confirm which database and environment you are connected to.
2. Run the WHERE clause as a SELECT first.
3. Wrap the change in a transaction where your database supports it.
4. Have a recent, tested backup before TRUNCATE or DROP in production.
5. Put DROP statements in reviewed migration scripts, never in ad-hoc consoles.
6. Check what depends on a table, such as views, foreign keys and reports, before dropping it.

Deleting large amounts of data safely

Removing millions of rows with one DELETE can lock the table for a long time, fill the transaction log and slow replication. A common pattern is to delete in small batches and repeat until nothing is left:

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

Run the statement in a loop, with a short pause between batches, until it reports zero rows. Each batch is a small transaction, so locks are short and other queries keep running. An index on the filter column, here created_at, keeps every batch fast.

For data that grows by date, such as logs and events, table partitioning is even better. Each month lives in its own partition, and removing old data becomes a quick operation that drops or truncates one partition instead of deleting rows one by one.

Soft deletes

Many applications never physically delete business records. They add a column such as deleted_at and set it instead, and queries then filter on deleted_at IS NULL. Soft deletes make recovery easy and keep history for audits, but every query must remember the filter, which is a good job for a view. Unique constraints also need care, because a soft-deleted row still holds its value. Privacy laws may require real deletion of personal data after a period, so soft deletes are not a substitute for a retention policy.

Final thoughts

DELETE is the precise, careful tool. TRUNCATE is the fast reset button, and DROP removes the table entirely. The biggest risk is assuming one database behaves like another, especially around rollback. When in doubt, test the command on a copy of the table in your own database first.

Frequently asked questions

Keep learning