DELETE vs TRUNCATE vs DROP in SQL Server

 1. DELETE

• Used to remove rows one at a time from a table.

• Can be filtered with WHERE clause.

• Each deleted row is logged in transaction log (slower).

• Identity column value does not reset unless explicitly used with DBCC CHECKIDENT.

• Can be rolled back if inside a transaction.

✅ Example:

DELETE FROM Employees WHERE DeptId = 2;

---

2. TRUNCATE

• Removes all rows from a table (no WHERE).

• Logs only page deallocations (faster than DELETE).

• Resets identity column to seed value.

• Cannot be used if:

  • Table is referenced by a foreign key.

  • Indexed views exist on the table.

• Can be rolled back if inside a transaction.

✅ Example:

TRUNCATE TABLE Employees;

---

3. DROP

• Deletes the entire table structure and data.

• Table definition, constraints, indexes, triggers — all removed.

• Cannot be rolled back (once committed).

• After DROP, table is gone from database; must be recreated to use again.

✅ Example:

DROP TABLE Employees;

---

4. Key Differences Table

Feature	DELETE	TRUNCATE	DROP
Removes	Specific rows (WHERE) or all	All rows	Entire table (structure + data)
WHERE Clause	✅ Allowed	❌ Not allowed	❌ Not applicable
Logging	Row-by-row (slow)	Minimal logging (fast)	Logs table removal
Identity Reset	❌ No	✅ Yes	❌ Not applicable
Rollback Possible	✅ Yes	✅ Yes	❌ No (unless inside transaction)
Foreign Key Restriction	✅ Allowed	❌ Not allowed if referenced	✅ Allowed (but drops constraints)
Table Structure Exists?	✅ Yes (after delete)	✅ Yes (empty table)	❌ No (removed completely)
Speed	Slower (row-by-row)	Faster (deallocate pages)	Fastest (drops object)

---

5. When to Use?

• DELETE → When you need to remove specific rows or want triggers to fire.

• TRUNCATE → When you want to remove all rows quickly and reset identity.

• DROP → When you want to completely remove table and free space.

---

6. Interview Tip

If interviewer asks:
👉 “Can we rollback TRUNCATE?”
✅ Answer: Yes, TRUNCATE can be rolled back if executed inside a transaction.

BEGIN TRAN;
TRUNCATE TABLE Employees;
ROLLBACK; -- restores data

👉 “Why TRUNCATE is faster than DELETE?”
✅ Answer: Because TRUNCATE logs only extent (page) deallocation, while DELETE logs each row removal.