What is the difference between TRUNCATE and DELETE + CHECKIDENT?

6

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

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

TRUNCATE :

TRUNCATE TABLE Exemplo

DELETE + CHECKIDENT :

DELETE FROM Exemplo
DBCC CHECKIDENT(Exemplo, reseed, 0);
    
asked by anonymous 24.07.2014 / 17:28

1 answer

9

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

    
24.07.2014 / 17:42