Why Truncate is faster than Delete?


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.  

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 *