Nic Cox used to be a Data Warehouse and ETL developer here at OptimalBI. Nic used to regularly battle it out whenever we did an exercise challenge. He has now spread his wings and is now living in London where is enjoying life on the Kiwi OE. You can read the original post of this blog at Nic Cox Business Intelligence here.
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.
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.
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.
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.
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.
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.
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
We will be running regular guest posts from Nic. You can read his blog, SQL Server Series: What are Isolation Levels? or visit his full site here. You can also read Nic’s blogs from his time with OptimalBI here.
OptimalBI run regular Data Vault course for business analysts, data architects, and business intelligence developers, Find out more here