PrismitaLEARN · PRACTICE · GROW

What is the difference between DELETE, DROP and TRUNCATE?

SQL Server T-SQL Basic Fresher1–2 Yrs Conceptual 1 min read

Quick Answer

DELETE (DML) removes rows — optionally filtered by WHERE — logging each, keeping the table. TRUNCATE (DDL) removes all rows fast by deallocating pages and resets identity, keeping the empty table. DROP (DDL) removes the table itself — structure, indexes, constraints, data, gone.

DELETE is row-level and can take a WHERE clause, it logs every row and leaves the table there. TRUNCATE wipes every row by just deallocating the pages, so it's near-instant and it resets the identity seed, but it's all rows or nothing and the empty table remains. DROP gets rid of the table entirely — the definition, indexes, permissions, everything. In SQL Server all three can be wrapped in a transaction and rolled back. TRUNCATE won't run if a foreign key points at the table.

  • All three are transactional in SQL Server and can be rolled back inside an explicit transaction.
  • TRUNCATE fails if a foreign key references the table; DROP fails if another object depends on it.
  • After DROP you must recreate the table to use it again; after TRUNCATE it's ready immediately.
  • Permissions differ — TRUNCATE/DROP need ALTER/ownership, not just DELETE.
All SQL Server questions More on T-SQL