TRUNCATE is a Data Definition Language (DDL) command used in SQL to quickly remove all rows from a table. Unlike DELETE, it does not log individual row deletions, making it faster for large tables.
Key Points
- Purpose
- Removes all data from a table.
- Resets any auto-increment counters (depends on database system).
- Syntax
TRUNCATE TABLE table_name;
- Differences Between TRUNCATE and DELETE
| Feature | TRUNCATE | DELETE |
|---|---|---|
| Logs | Minimal | Full row-level logging |
| WHERE clause | Not allowed | Allowed |
| Speed | Faster | Slower for large tables |
| Trigger activation | Usually does not fire triggers | Triggers fire |
- Use Cases
- Clearing a table before loading new data.
- Quickly removing all records without affecting table structure.
- Precautions
- Cannot be rolled back in some databases (like MySQL with non-transactional tables).
- Removes all rows, so be sure you don’t need the data.
Example
TRUNCATE TABLE employees;
This command removes all records from the employees table but keeps the table structure intact.