TRUNCATE and DELETE

I started the journey for this blog because I wanted to make sure I understood all the differences between TRUNCATE and DELETE. 

What I discovered is that the main difference is that TRUNCATE is DDL and DELETE is DML. As if there aren’t enough acronyms to remember, here you go.

DDL – Data Definition Language (used to define data structures)

            Examples: ALTER, CREATE, DROP, TRUNCATE TABLE

DML – Data Manipulation Language (used to retrieve, store or modify data)

Examples: SELECT, INSERT, UPDATE, DELETE

Both TRUNCATE and DELETE are logged, but in different ways. TRUNCATE TABLE deallocates the data pages used to store the table data, recording only the page deallocations in the transaction log. DELETE removes rows one at a time, recording an entry in the transaction log for each row. This is what makes TRUNCATE faster than DELETE.

TRUNCATE cannot be performed on a table with foreign key constraints. DELETE will remove any rows that will not violate a constraint.

Identity columns are reset to the default value with TRUNCATE, whereas DELETE retains the identity seed.

You can also only use a WHERE clause with DELETE.

Because TRUNCATE TABLE does not log individual rows, it cannot activate a trigger.

And finally, DELETE can be rolled back using the transaction log but once TRUNCATE is committed it cannot. You can, however, reverse a TRUNCATE by wrapping it in a transaction.

BEGIN TRANSACTION

TRUNCATE TABLE <TableName>

ROLLBACK