DELETE vs TRUNCATE vs DROP

"Remove the data" can mean three very different things: delete some rows, empty the whole table quickly, or destroy the table. Mixing these up is one of the most expensive beginner mistakes — and one of the most common interview questions.

Definitions

DELETE (DML) removes rows from a table — some rows (with WHERE) or all rows — one row at a time, logging each deletion. The table remains.

TRUNCATE TABLE removes all rows from a table at once by releasing the data pages, which is much faster than DELETE. It cannot use WHERE. The table structure remains. (SQL Server classifies it as a DDL statement.)

DROP TABLE (DDL) removes the whole table — structure and data. The table no longer exists.

Syntax

DELETE FROM players WHERE id = 6;   -- some rows
DELETE FROM players;                -- all rows, slowly

TRUNCATE TABLE players;             -- all rows, fast (SQL Server)

DROP TABLE players;                 -- the whole table

DELETE vs DROP — do not mix them up!

The full comparison

Note: A common myth is "TRUNCATE can't be rolled back". In SQL Server it can, when run inside an explicit BEGIN TRANSACTION. In MySQL and Oracle, TRUNCATE auto-commits and can't be rolled back.

Example — the same table, three ways

Start: players has 6 rows.

IDENTITY after DELETE vs TRUNCATE (SQL Server)

-- fans has fan_id INT IDENTITY(1,1) and 3 rows (ids 1, 2, 3)
DELETE FROM fans;
INSERT INTO fans (name) VALUES ('Ravi');   -- gets fan_id = 4 (counter continues)

TRUNCATE TABLE fans;
INSERT INTO fans (name) VALUES ('Ravi');   -- gets fan_id = 1 (counter reset)

Which one should I use?

Common mistakes

• ❌ TRUNCATE TABLE players WHERE id = 6 → syntax error. ✅ TRUNCATE has no WHERE — use DELETE.
• ❌ DROP when you meant to empty the table → the structure is gone too. ✅ DELETE / TRUNCATE.
• ❌ TRUNCATE on a table referenced by a foreign key → error. ✅ Use DELETE, or drop the foreign key first.
• ❌ Saying "DELETE is DDL". ✅ DELETE is DML; DROP is DDL.

SQL Server vs MySQL / PostgreSQL / SQLite

TRUNCATE TABLE exists in SQL Server, MySQL and PostgreSQL. SQLite has no TRUNCATE — DELETE FROM table without WHERE is automatically optimised instead. That's why the playground examples use DELETE.

Interview tip

Q: Difference between DELETE, TRUNCATE and DROP? A: DELETE is DML and removes selected (or all) rows one by one, supports WHERE, fires triggers and keeps the identity counter. TRUNCATE removes all rows at once, is much faster, has no WHERE, resets the identity and is blocked by foreign keys. DROP removes the whole table — structure and data. In SQL Server all three can be rolled back inside an explicit transaction.