Source: Pixabay

Whilst they ultimately perform a very similar task, the TRUNCATE and DELETE statements behave very differently.  The flexibility they allow varies, the way they are logged is different, one performs better than the other, plus there are a raft of other differences.  This blog will give you a better understanding of the differences between the two commands.

End Result

It seems strange to start something with the end, but to understand the other workings of DELETE and TRUNCATE, we first must understand what they achieve at the end.
When you execute the TRUNCATE or DELETE command using either of the following two syntaxes

TRUNCATE TABLE schema.table
DELETE FROM schema.table

You are commanding the database to completely empty the table of all data.
You don’t have to empty the entire table however, the DELETE statement gives you much more flexibility over what data to delete than the TRUNCATE command.  TRUNCATE is pretty much all or nothing, but with a DELETE you can specify a where clause to only delete data that matches that filter condition.

DELETE FROM schema.table WHERE column = 'X'

TRUNCATE does, however, allow you to only remove certain partitions within a table, a glimmer of flexibility but nowhere near what DELETE provides.

TRUNCATE TABLE schema.table WITH (PARTITIONS (1 to 3))

The end result of both commands is to remove data from a table, but DELETE allows you to pick what data to remove, TRUNCATE just gets rid of the lot within the entire table or specified partitions.

Picture it:
You have two green and two blue post-it notes on a desk.  You remove all the post-it notes and put them in the fire destroying them forever and leaving an empty desk.

In this picture it, the post-it notes represent rows in a table.  Removing all the post-it notes and destroying them forever represents a TRUNCATE.

Picture it:
You have two green and two blue post-it notes on a desk.  You remove the blue post-it notes one by one, safely putting them out of sight.  You are left with only the two green post-it notes.

In this picture it, the post-it notes represent rows in a table.  Removing the blue post-it notes represents executing a DELETE with a Where clause.  Putting the blue post-it notes out of sight represents the DBMS logging the deletions.

Logging and Recovery

This is an important concept to know when choosing whether to use TRUNCATE or DELETE.  I’m not going to delve too deep into the inner workings of SQL Server logging, as that could be another blog post in itself, but give just enough information for you to understand why the logging is important.
When the DELETE statement is issued, the DBMS logs each individual deleted row.  So while the row may no longer exist in the physical table, it is still sitting around in the log tables.  This means that if you accidentally delete rows that you shouldn’t have, you can restore them using the log files.
The TRUNCATE command performs much less logging than its counterpart.  When the TRUNCATE statement is issued, the DBMS will log the deallocation of data pages.  Individual rows are not logged, and this means it is much harder to restore your data if you accidentally TRUNCATE it all.  You can easily restore your data from a TRUNCATE if it is run within a transaction.  The TRUNCATE statement also requires less log file space due to the lower amount of logging.

Performance

The performance between the two statements varies greatly due to the differences in logging.  Since the TRUNCATE statement requires much less logging by the DBMS, it is more efficient and performs much faster.
The DELETE statement, on the other hand, performs poorly in comparison as it has to log each deleted row.  DELETE also has to deal with filter conditions used within the statement. Just like a SELECT statement, the more complicated the conditions within the DELETE statement, the longer it will take to execute.

Table Locks

The TRUNCATE statement always locks the entire table.  This means if another transaction is currently modifying or has the table locked in any way, the TRUNCATE will hang until either that lock is release or the executions times out.  Once the TRUNCATE has locked the table, no other transactions will be able to access the table.
The DELETE statement only locks each row as it deletes it.  Provided no other transactions have a lock on the table, or the row being deleted at the time, the statement will succeed and other transactions will be able to concurrently access the table.

Foreign Key Constraints

One of the more noteworthy differences is that you cannot TRUNCATE a table that has any foreign key constraints.  You must first remove the constraint, TRUNCATE the table and then recreate the constraints. The DELETE statement allows you to remove rows with foreign key constraints, keeping those constraints in place.

Other Differences

The TRUNCATE statement will not enable any delete triggers on your tables, the DELETE statement will as this is what these triggers are developed for.
The TRUNCATE statement will reset any identity columns back to their original defined seed value.  The DELETE statement will continue the identity columns as is from the next expected value.
The TRUNCATE command is DDL (Data Definition Language) and the DELETE command is DML (Data Manipulation Language).
The DELETE statement can be used within a MERGE statement.

Wrap Up

There is a time and a place for both the TRUNCATE and DELETE statements.  TRUNCATE performs much faster but does not present you with the same amount of flexibility or recovery options as DELETE.  The DELETE statement performs much more logging than TRUNCATE, and also allows you to specify filter conditions for removing your data.
I hope this blog provided you with some new insights and knowledge.  Please feel free to check out my other blogs.
Until next time, Nic