Delete vs Truncate

Delete

  • It is a DML command
  • We can use filters in WHERE clause to delete the data
  • Removes specified data only
  • Deletes one row at a time
  • Records removed data in transaction logs
  • It does not resets the identity field of the table
  • Triggers can be executed with Delete statement.
  • Slower than Truncate since it has to maintain logs and checks for WHERE condition

Truncate

  • It is a DDL command
  • We can’t use WHERE condition
  • Removes all the data
  • Removes all the data at a time
  • It does not records into transaction logs
  • Resets the Identity field
  • Triggers can not be executed with Truncate.
  • Faster than DELETE

Why Truncate is faster?  😕

Most of the developers might be in a misconception that Truncate is faster because delete contains where condition and it needs to filter and then delete. But Truncate does not contain any filter condition. But this cannot be the correct reason in all cases.

What if a developer wants to delete all records in a table? Do both of them give same performance in this case?

Undoubtedly NO.

There are few hidden secrets of DELETE and TRUNCATE. Let us see what is going to happen when we DELETE or TRUNCATE data from a table.

When we are Deleting the records from a table, it does the following.

  • Read the required records based on Where condition
  • Deletes each record one by one and also updates the transaction log
  • Update Indexes
  • Check for Constraints on the table
  • Check for DELETE triggers on the table and trigger them to perform action

But Truncate,    

does minimal logging. It just De-allocate all the data pages and logs only the de-allocation information. But not logs entire data as Delete.  That’s it. It does not do all the above which DELETE do.

That’s why TRUNCATE is far faster than DELETE.

              Most of the people are in the misconception that Truncate cannot be rolled back. But it can be. Yes. we can Rollback Truncate if it is in the same session and transaction.

Suggestion:

When our requirement is to delete all the rows in a huge table, just use Truncate instead f Delete. Since Delete logs each and every record, there is change of crashing log when we are deleting records continuously for hours.

Happy Reading :)

Leave a Reply

Your email address will not be published. Required fields are marked *