TRUNCATE TABLE vs DELETE FROM table (SQL Server)

---

Download xSQL Compare Bundle, starting from $0!

---

DELETE FROM [table name] without using a WHERE clause and TRUNCATE TABLE [table name]  will have the same effect on the database, all the rows will be deleted from your table (assuming that the deletion of those rows does not violate any constraints). So which one should you use? The answer lies in the differences between those two statements:

• DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table's data, and only the page de-allocations are recorded in the transaction log. 
• TRUNCATE resets the counter used by an identity for new rows to the seed for the column, whereas DELETE does not reset the counter.
• TRUNCATE TABLE cannot be used on a table referenced by a FOREIGN KEY constraint even if no violation of the constraint would occur, in fact a TRUNCATE on a table referenced by a FOREIGN KEY would fail even if the table is empty. The DELETE on the other side will succeed if deleting the rows does not violate the constraint.
• TRUNCATE does not activate the triggers whereas a DELETE will activate the trigger for every row that is deleted.
• TRUNCATE TABLE may not be used on tables participating in an indexed view.

Truncate table is a lot faster and it consumes significantly less resources than the Delete from table statement but you have to choose the right statement based on the particular requirements you have not simply based on which one runs faster.

If you read this article chances are that our SQL Data Compare tool would be very helpful to you - it is free for SQL Server Express with no limitations. Download your copy from here