Many times in interviews, the interviewer will ask about the difference between SQL DELETE and TRUNCATE commands, so to answer this question, I have decided to write this blog to assist job seekers and those who want to know the difference between the two commands.

SQL Truncate

Truncate will remove all the rows from a table but strucure , and there will be no data in the table, but table strucure will remain as it is, such as columns and rows after we run the truncate command.

The following are some keypoints.

• Truncate is faster and uses fewer system and transaction log resources than Delete.
• The data is removed by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
• Truncate removes all rows from a table, but the table structure, including its columns, constraints, indexes, and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
• You cannot use Truncate on a table referenced by a FOREIGN KEY constraint. Because Truncate table is not logged, it cannot activate a trigger.
• Truncate cannot be rolled back.
• Truncate is a DDL command.
• Truncate resets the identity of the table.

SQL Delete

The Delete command removes the rows from a table based on the condition that we provide with a Where clause.

 
The following are some keypoints.

If the current session is not closed, delete and truncate can both be rolled back when surrounded by transactions. If truncate is written in the Query Editor surrounded by transactions and if the session is closed, it can not be rolled back, but delete can be rolled back.

Summary

I hope this small blog is helpful for all job seekers and other readers. If you have any suggestions, then please contact me.