PrismitaLEARN · PRACTICE · GROW

What is the difference between TRUNCATE and DELETE?

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

Quick Answer

DELETE removes rows one at a time, logs each, fires triggers, can have a WHERE clause, and doesn't reset identity. TRUNCATE deallocates whole data pages (minimally logged, very fast), resets the identity seed, doesn't fire DELETE triggers, and removes all rows. Both are transactional and can be rolled back.

DELETE is row-by-row: it logs every row, fires triggers, respects a WHERE clause, and leaves the identity counter where it is. TRUNCATE just deallocates the table's pages, so it's minimally logged and near-instant, it resets the identity seed to the start, and it doesn't fire delete triggers — but it's all-or-nothing, no WHERE. Both are fully transactional, so the “you can't roll back a truncate” thing is a myth as long as you're in an explicit transaction. TRUNCATE also won't run if any foreign key points at the table.

  • TRUNCATE fails if the table is referenced by a foreign key (even with no child rows) — SQL Server 2016+ allows truncating individual partitions.
  • Common myth: “truncate can't be rolled back” — it can, inside an explicit transaction.
  • DROP removes the table structure entirely; TRUNCATE keeps the empty table.
  • Need identity reset without truncate: DBCC CHECKIDENT.
All SQL Server questions More on T-SQL