What difference between TRUNCATE and DELETE + CHECKIDENT?
Both perform the same action ( delete + zero PK value), but in performance what is the difference between them?
example: when having more records is it recommended to use which form?
Truncated :
TRUNCATE TABLE Exemplo
DELETE + CHECKIDENT :
DELETE FROM Exemplo
DBCC CHECKIDENT(Exemplo, reseed, 0);
1 answers
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 DELETE Command is slower and safer (you have the security of being able to do rollback every time). Another peculiarity of SQL Server is that, for data integrity reasons, it is not possible to run the {[0 command]} against a table referenced by a foreign key, in this case the way is to execute a DELETE (or disable the constraint).
Command DELETE requires permission for DELETE, Command TRUNCATE requires permission for ALTER.
Reference: MSDN-Truncate Table