There is always a confusion about delete and truncate specially when this question comes suddenly in interview.
Most of the time, the simple answer is that there are logs created in Delete and no logs in case of truncate. But that is also not fully rue. The correct answer is : In case of delete, as number of log entries are created as number of records being deleted means one log per record deleted. In case of truncate, one log entry is created for the whole truncate.
Some more differences along with above main one can be listed as :
- One Log entry for the whole transaction and one log per record deleted in case of delete.
- Truncate deletes all records, but you can put where clause in case of delete.
- The Identity column counter value resets to 1 in case of Truncate, in case of delete it stays to the last inserted record
- Delete can be rolled back, truncate cannot. In other words, truncate is committed transaction.