What's the difference between TRUNCATE and DELETE in SQL

Knowing when to TRUNCATE and when to DELETE

TRUNCATE and DELETE can both remove rows from a table, but they do it in fundamentally different ways. Let`s dive in for a better understanding.

TRUNCATE digested:

• TRUNCATE is a DDL operation that quickly empties a table by removing all rows in bulk.

• It recovers the space immediately and resets the table for immediate reuse.

• Being a bulk operation, TRUNCATE does not care about individual rows—no specific rows are targeted or recorded, making it a non-logged operation.

• Because TRUNCATE is not selective, DML triggers do not fire during its operation.

• In the deal of lock management, TRUNCATE goes for the whole table at once, enforcing an exclusive lock.

• Its awareness of identity columns makes it reset the associated sequences in SQL Server.

• The operation revives any unusable indexes, turning them usable once again.

TRUNCATE TABLE Employee; -- You're all fired!


DELETE in depth:

• On the contrary, DELETE is a DML operation. It can fundamentally remove all or just a selective subset of rows, it all depends on the WHERE clause.

• With visibility at the row level, DELETE logs its operations with much detail, making it a fully logged operation.

• Deploying DELETE on a table does not affect index usability, indexes remain the status quo.

• A more social operator, uses shared table locks and allows others to interact via row-level locks.

• More sensitive to identity columns, maintains their sequence, with no index resetting.

• As every row counts for it, fires DML triggers for each deletion.

DELETE FROM Employee WHERE name='John Doe'; -- Bye, John! 


Starring: DELETE and TRUNCATE

Let's delve deeper into some of the distinguishing characteristics:

Transaction logs, Redo and Undo data

TRUNCATE simplifies the affair—it requires less redo/undo data, impacting transaction logs negligible.

TRUNCATE TABLE logs; -- Let's forget the past! (Don't actually do this!)


while DELETE, records every row deleted—a verbose story in the transaction logs, building a significant redo/undo data.

DELETE FROM logs WHERE log_date < SUNDAY; -- Week starts on Monday!


Triggers and Flashbacks

TRUNCATE ignores DML triggers—not triggering them during its operation while DELETE will trigger DML triggers associated with the table for each row.

-- Haha, nice TRY DUP...
TRUNCATE TABLE tries;  -- No Triggers invoked
DELETE FROM tries;  -- Every time you fire this, a Trigger gets its wings


Also, a point to note, after a TRUNCATE operation, no flashbacks can be done to return the state before the operation.

Privileges & Constraints

With the right privileges, TRUNCATE can be issued even without DELETE privileges on the table.

GRANT SELECT, TRUNCATE ON employees TO user;  -- Truncate-action superpower without delete. Cool, right?


In the context of a foreign key constraint, TRUNCATE can't be performed if a table has a reference but DELETE can still be performed, if you're careful not to violate the constraint.

-- So you're saying there's a chance? 
DELETE FROM parent_table WHERE id NOT IN (SELECT FOREIGN_ID FROM child_table);  -- Yes, YES there is!
TRUNCATE TABLE parent_table;  -- Sorry, it's a NO from me, dog🐶.


Custom Cases: SQL Server vs Memory-optimized Tables

TRUNCATE in SQL Server can reset sequences for Identity column types. However, it doesn't apply when memory-optimized tables are in play.

Practical considerations

Performance 💹

TRUNCATE is much faster when the goal is to remove all rows from the table, thanks to its bulk operation nature.

Scalability 📈

When operating on larger datasets, TRUNCATE outperforms due to reduced logging overhead. Performing DELETE without a WHERE clause on large tables can cripple performance.

Concurrency 🧩

For applications sensitive to locking and concurrency, the row-level operation of DELETE shines through, offering less disruption of database activities.

Data recovery and audit 📚

DELETE wins the hearts of DBAs in cases where recovery and audit requirements are stringent due to its logged nature and capability to roll back transactions.