What is the difference between TRUNCATE and DELETE + CHECKIDENT?

Question

Both perform the same action (delete + reset the PK value), but in performance what is the difference between them?

Example: When you have more records it is recommended to use which form?

TRUNCATE:

TRUNCATE TABLE Exemplo

DELETE + CHECKIDENT:

DELETE FROM Exemplo
DBCC CHECKIDENT(Exemplo, reseed, 0);

Answer 1

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 resets the identity column counter as you noticed.

The command DELETE is slower and safer (you have the security of being able to do rollback at all times). Another feature of SQL Server is that, for data integrity reasons, it is not possible to execute the command TRUNCATE against a table referenced by a Foreign key, in which case the way is to execute a DELETE (or otherwise disable the Constraint).

The command DELETE requires permission to DELETE, the command TRUNCATE requires permission to ALTER.

---

Reference: MSDN - Truncate Table