The TRUNCATE TABLE command is an operation that quickly and completely deletes all rows from a table by releasing the data storage pages, instead of processing and logging each individual deletion.
In the previous article, we learned to delete data with DELETE. We saw that if we execute a DELETE FROM Table without WHERE, we delete all rows.
So, if we want to clean an entire table, do we use DELETE? The answer is: if you care about performance, NO.
To completely empty a table, SQL Server offers us a specialized, much faster and more drastic command, TRUNCATE TABLE.
| Characteristic | DELETE (without WHERE) | TRUNCATE TABLE |
|---|---|---|
| Speed | Slow (proportional to the number of rows) | Very fast (constant) |
| Transaction Log | High (logs each row) | Minimal (logs pages) |
| Filters | Allows WHERE | All or nothing |
| Identity | Does not reset the counter | Resets to the seed |
| Foreign Keys | Allowed (if no child records) | Blocked if the relationship exists |
| Triggers | They are executed | They are ignored |
If DELETE is a “fine and elegant” operation that works row by row, TRUNCATE is a wrecking ball.
What is TRUNCATE TABLE?
TRUNCATE TABLE deletes all rows from a table, but keeps the table’s structure intact (columns, constraints, indexes, etc.).
The syntax is extremely simple:
TRUNCATE TABLE Pedidos;
For practical purposes, the visual result is the same as a DELETE without WHERE: the table is left empty. However, internally they work very differently.
Key Differences with DELETE
Understanding the difference between these two commands is a classic technical interview question. Here are the key points:
Performance and Transaction Log
This is the big difference.
- DELETE: It is a row-by-row logged operation. If you delete 1 million records, SQL Server writes 1 million entries to the transaction log (“I deleted row 1”, “I deleted row 2”…). This is slow and fills up the disk.
- TRUNCATE: It is a data page level logged operation. Instead of deleting rows one by one, it simply marks the disk pages where the data was as “free”. It’s almost instantaneous, regardless of whether there are 10 rows or 10 million.
Resetting IDENTITY columns
If you have an auto-incrementing column (IDENTITY), the behavior changes:
- DELETE: Does not reset the counter. If you deleted up to ID 100, the next record you insert will be 101.
- TRUNCATE: Resets the counter to its original seed value (usually 1). The next record will be 1 again.
If you are cleaning tables in a test environment and want to start from scratch “for real” (with IDs starting from 1), always use TRUNCATE.
Triggers
- DELETE: Activates triggers defined as
ON DELETE. - TRUNCATE: Does not activate
ON DELETEtriggers. Since the operation does not delete row by row, the engine doesn’t bother notifying the triggers.
Constraints: When you CANNOT use TRUNCATE
Despite being wonderful for its speed, TRUNCATE has a very important limitation related to referential integrity.
You cannot execute TRUNCATE on a table that is referenced by a Foreign Key.
Even if the child table is empty, SQL Server won’t let you.
-- If 'Pedidos' has an FK pointing to 'Clientes'...
TRUNCATE TABLE Clientes;
Error: Cannot truncate table ‘Clientes’ because it is being referenced by a FOREIGN KEY constraint.
The solution? To truncate a parent table, you must first delete (or disable) the Foreign Key constraint, truncate the table, and then recreate the constraint.
If this seems like too much work, then you’ll have to use DELETE (which does work, as long as you delete the children first)
