DELETE vs TRUNCATE vs DROP
| Feature | DELETE |
TRUNCATE |
DROP |
|---|---|---|---|
| Purpose | Removes selected or all rows | Removes all rows | Removes the entire table |
WHERE condition |
Supported | Not supported | Not applicable |
| Table structure remains | Yes | Yes | No |
| Data remains | Selected rows may remain | No | No |
| Identity resets | No | Yes | Table is removed |
| Speed | Slower | Faster | Fast |
| Command type | DML | DDL | DDL |
| Triggers | DELETE triggers execute |
DELETE triggers don't execute |
DELETE triggers don't execute |
| Can roll back in SQL Server | Yes, inside a transaction | Yes, inside a transaction | Yes, inside a transaction |
1. DELETE
Removes selected rows or all rows while keeping the table.
DELETE FROM Employees
WHERE DepartmentId = 10;Remove all rows:
DELETE FROM Employees;Use DELETE when you need:
-
A
WHEREcondition -
Row-by-row deletion
-
DELETEtriggers to execute
2. TRUNCATE
Removes all rows but keeps the table structure.
TRUNCATE TABLE Employees;Key points:
-
Cannot use
WHERE -
Usually faster than
DELETE -
Uses minimal transaction-log records
-
Resets the identity value
-
Cannot truncate a table referenced by an active foreign-key constraint
-
Does not execute
DELETEtriggers
3. DROP
Removes the complete table, including its data and structure.
DROP TABLE Employees;It removes:
-
All rows
-
Columns
-
Constraints
-
Indexes
-
The table definition
To use the table again, it must be recreated.
Simple example
Assume Employees has 1,000 rows:
DELETE FROM Employees WHERE DepartmentId = 10;Removes only department 10 employees; the table remains.
TRUNCATE TABLE Employees;Removes all 1,000 rows; the empty table remains.
DROP TABLE Employees;Removes all rows and the table itself.
Easy memory tip
-
DELETE→ Remove rows -
TRUNCATE→ Empty the table -
DROP→ Remove the table