~1 min read|
I was fiddling with some test databases recently when a colleague suggested I could wipe the entire database (without dropping the table) using a
This got me wondering though - what’s the difference between
The major differences seem to be:
TRUNCATEis typically faster than
TRUNCATEdoes not log the records deleted, whereas
TRUNCATElocks the entire table.
DELETElocks the rows before deleting them.
DELETEthen 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)
TRUNCATEdoesn’t take any conditions, whereas
DELETEcan accept a
WHEREclause. The consequence -
TRUNCATEcan only “reset” a table to its empty state.
DELETEcan be used for more fine-tooth’d removal of rows.
Is approximately the same as (though the mechanisms differ):
Whereas there’s no
TRUNCATE equivalent to:
DELETE employees e WHERE e.firstName = 'John';
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!