When to prefere a truncate over a batch-delete?

Question

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.

Answer 1

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.

Answer 2

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.