Truncate Table in SQL
The TRUNCATE TABLE statement in SQL is used to remove all rows from a table quickly and efficiently. Unlike the DELETE statement, TRUNCATE TABLE deletes data without logging each row deletion, which makes it faster, especially for large tables.
Key Characteristics of TRUNCATE TABLE
Deletes all rows from the specified table.
Cannot use a
WHEREclause — it always removes every row.Resets any identity (auto-increment) counters to their seed value in many database systems.
Usually cannot be rolled back once executed (depending on the database and transaction settings).
Does not fire DELETE triggers in most database systems.
Removes data but does not remove the table structure or its indexes.
Syntax
TRUNCATE TABLE table_name;Example
TRUNCATE TABLE Customers;This command will quickly remove all rows from the Customers table.
Differences from DELETE
| Aspect | TRUNCATE TABLE | DELETE |
|---|---|---|
| Deletes rows | All rows (no filtering) | Can delete specific rows with WHERE |
| Logging | Minimal logging | Logs each row deletion |
| Triggers | Usually does not fire | Fires DELETE triggers |
| Rollback | Often cannot be rolled back | Can be rolled back |
| Identity reset | Resets identity counter | Does not reset identity |
| Speed | Faster | Slower (especially for large tables) |
Notes
In some databases,
TRUNCATE TABLEis a DDL (Data Definition Language) command and may cause an implicit commit.Always use with caution because it removes all data irreversibly in many systems.
If you want, I can provide examples of TRUNCATE TABLE usage for your specific SQL database!
