Differences Between Truncate and Delete in SQL Server
In this article, I will give information about the differences between Truncate and Delete in SQL Server.
We’ve all wondered about the differences between Truncate and Delete, which we all use in SQL Server.
- Both commands delete the data inside the table, not the structural content of the table.
- Truncate consumes less log resources while Delete command consumes a lot of log resources. That’s why the Truncate command works faster than the Delete command. As for the reason, the Delete command deletes the lines one by one and makes a transaction log entry for each line it deletes.
- When using the delete command, we have the possibility to add the where condition to our query. There is no such facility in the truncate command, it deletes all records.
- If there is an identity column in our table, when the Delete is done, the column continues to count from where it left off. When Truncate Table is made, it will start counting from the beginning.
- If there is a Trigger for the Delete operation on the table, the Trigger will not be triggered when we make a Truncate Table. In the delete command, the Trigger is triggered.
- Tables containing Foreign Keys are not made Truncate Table. Delete command should be used.
As you can see, we have learned the differences between Truncate and Delete in SQL Server.
Good luck to everyone in business and life.