In terms of performance TRUNCATE
is more efficient. The main reason for this is that the command does not write line by line deleted in the transaction log. In the case of SQL Server the command also already resets the identity column counter as you noticed it.
The DELETE
command is slower and safer (you have the security of being able to rollback
every time). Another peculiarity of SQL Server is that, due to data integrity issues, it is not possible to execute the TRUNCATE
command against a table referenced by a foreign key , in which case the way is to execute a DELETE
(or disable constraint ).
The DELETE
command requires permission for DELETE
, the TRUNCATE
command requires permission for ALTER
.
Reference: MSDN - Truncate Table