Delete All Rows in SQL
? How to Delete All Rows in SQL
If you want to remove all rows from a table but keep the table structure (columns, constraints, indexes) intact, you have two common options:
1. Using DELETE
DELETE FROM table_name;Deletes all rows one by one.
Can be slower on large tables.
Can be used inside transactions (can rollback).
Example:
DELETE FROM employees;2. Using TRUNCATE
TRUNCATE TABLE table_name;Removes all rows faster than
DELETEby deallocating data pages.Usually can’t be rolled back in some databases (depends on DBMS).
Resets identity/auto-increment counters in many DBMS.
Example:
TRUNCATE TABLE employees;Key Differences
| Aspect | DELETE All Rows | TRUNCATE TABLE |
|---|---|---|
| Speed | Slower (row by row) | Faster (deallocates pages) |
| Transactional | Yes (can rollback) | Usually no rollback |
| Triggers | Fires DELETE triggers | Usually does not fire triggers |
| Resets Identity | No | Yes |
If you want, I can explain how this works in specific databases like MySQL, SQL Server, or PostgreSQL!
