Efficient Difference Between TRUNCATE And DELETE

In this post, we want to discuss the Difference Between Truncate and Delete in SQL Server. Before we get started, if you want to know about SQL table type, please go through the following article: Table type Parameters in SQL Server.

While working with the database we often need to delete data, sometimes to create more space, sometimes just remove all data to make the table ready for the next day’s transaction, or sometimes just selectively remove stale data. SQL provides a couple of handy commands to remove data e.g. truncate, delete and drop. The last one is a bit different because instead of removing data it just deletes the table.

What is the difference between truncate and delete command in SQL or when to use truncate vs delete is one of the most frequently asked SQL interview questions? Knowledge of this critical command is not only useful for interviews but also while working with a big database with a large number of records, especially while writing SQL scripts for purging transaction databases.

An Incorrect choice of SQL command can result in either very slow processor can even blow up the log segment; if too much data needs to be removed and log segment is not enough. That is why it is critical to know when to use truncate and delete commands in SQL.

TRUNCATE vs DELETE in SQL:

The first and most important difference between the TRUNCATE and DELETE command in SQL is that truncate does not log row-level details while delete is logged. Since TRUNCATE is not logged, it’s not possible to roll back it e.g. in Oracle. However, some databases may provide rollback functionality for truncate, on the other hand, DELETE is always logged and can be rolled back.
Due to the above reason, the TRUNCATE command is much faster than DELETE, and due to some reason, you should not use DELETE to remove large data set, since every delete operation is logged, it may be possible that your log segment gets filled and blew up if you try to empty a large table using the DELETE command.
One syntactical difference between DELETE and TRUNCATE is that the former is a DML command while the latter is a DDL command.
Another critical difference between TRUNCATE and DELETE commands in SQL is that the former reset any Identity column while DELETE retains the value of the identity column.
DELETE command work at row level and acquire the row-level lock while TRUNCATE locks the whole table, instead of individual rows.
One more difference between TRUNCATE vs DELETE comes from the fact we discussed in the first option, it does not activate the trigger, because it does not log individual row deletion, while DELETE activates the trigger, because of row-level logging.
Truncate is usually used to remove all data from tables e.g. in order to empty a particular table and you can define any filter based upon the WHERE clause, but with DELETE, you can define the condition in the WHERE clause for removing data from tables.
Since TRUNCATE is a DDL operation, it is automatically get committed, on the other hand, DELETE is not auto-commit.