delete vs truncate in sql

2020-07-06

 | 

~1 min read

 | 

194 words

I was fiddling with some test databases recently when a colleague suggested I could wipe the entire database (without dropping the table) using a TRUNCATE command.

This got me wondering though - what’s the difference between TRUNCATE and DELETE?

The major differences seem to be:

  1. Performance: TRUNCATE is typically faster than DELETE
  2. Visibility: TRUNCATE does not log the records deleted, whereas DELETE does.
  3. Locking: TRUNCATE locks the entire table. DELETE locks the rows before deleting them. DELETE then is a better choice for a table with many concurrent users (since they’ll still be able to transact with rows that are not being actively deleted)
  4. Conditions: TRUNCATE doesn’t take any conditions, whereas DELETE can accept a WHERE clause. The consequence - TRUNCATE can only “reset” a table to its empty state. DELETE can be used for more fine-tooth’d removal of rows.
TRUNCATE employees;

Is approximately the same as (though the mechanisms differ):

DELETE employees;

Whereas there’s no TRUNCATE equivalent to:

DELETE employees e
  WHERE e.firstName = 'John';

Sources:


Hi there and thanks for reading! My name's Stephen. I live in Chicago with my wife, Kate, and dog, Finn. Want more? See about and get in touch!