Difference between Delete and Truncate

The TRUNCATE statement deletes all rows from the target table. Unlike the DELETE statement,
it doesn’t have an optional filter, so it’s all or nothing. As an example, the following statement
truncates the table Sales.MyOrderDetails.

TRUNCATE TABLE Sales.MyOrderDetails;

After executing the statement, the target table is empty.

The DELETE and TRUNCATE statements have a number of important differences between
them:

1. The DELETE statement writes significantly more to the transaction log compared to the TRUNCATE statement. For DELETE, SQL Server records in the log the actual data that was deleted. For TRUNCATE, SQL Server records information only about which pages were deallocated. As a result, the TRUNCATE statement tends to be substantially faster.
2. The DELETE statement doesn’t attempt to reset an identity property if one is associated with a column in the target table. The TRUNCATE statement does. If you use TRUNCATE and would prefer not to reset the property, you need to store the current identity value plus one in a variable (using the IDENT_CURRENT function), and reseed the property with the stored value after the truncation.
3. The DELETE statement is supported if there’s a foreign key pointing to the table in question as long as there are no related rows in the referencing table. TRUNCATE is not allowed if a foreign key is pointing to the table—even if there are no related rows in the referencing table, and even if the foreign key is disabled.
4. The DELETE statement is allowed against a table involved in an indexed view. A TRUNCATE statement is disallowed in such a case.
5. The DELETE statement requires DELETE permissions on the target table. The TRUNCATE statement requires ALTER permissions on the target table.

When you need to delete all rows from a table, it is usually preferred to use TRUNCATE
because it is significantly faster than DELETE. However, it does require stronger permissions,
and is more restricted.