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
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.
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.