1

When I browse DBA-Reactions I understand nearly all the .gifs but with this one I have trouble:

https://twitter.com/DBAReactions/status/613840893322338304

I mean truncate obviously doesn't create the transaction log as a batch delete does but still there are cases where you are pretty sure that you can simply truncate a complete table. Are there any other concerns besides that? So basically I would love to understand the complete difference.

  • The reaction is more because 'it's faster' is not necessarily a valid reason to use TRUNCATE over DELETE, rather than TRUNCATE being a bad thing – Mark Sinkinson Jul 16 '15 at 10:39
  • ok, but why not use the faster way if it's not bad :) ? – RayofCommand Jul 16 '15 at 11:41
  • 1
    Because speed is not the only difference between the two. – Aaron Bertrand Jul 16 '15 at 14:05
3

There are certain circumstances where you cannot use the TRUNCATE TABLE command and must use the DELETE command instead.

You cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

• Participate in an indexed view.

• Are published by using transactional replication or merge replication.

For tables with one or more of these characteristics, use the DELETE statement instead.

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

There is a good explanation on MSDN:

https://msdn.microsoft.com/en-gb/library/ms177570.aspx

Also, see this question on Stack Overflow which has a lengthy discussion on the topic.

1

As you say truncate doesn't use the transaction log resources a DELETE would. TRUNCATE is not technically classed as a minimally logged operation but is very efficiently logged as it just deallocates the pages and extents relevant to the data. Nothing wrong with TRUNCATE, its logged you can rollback and its quick.

Remember TRUNCATE wont fire triggers, if you need to fire triggers then you will eed to use DELETE in maybe manageable batches to reduce locking etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.