Should I TRUNCATE TABLE before I DROP TABLE to avoid logging overhead?

Question

I understand that using TRUNCATE is a minimally logged operation and does not log the deletion of each record while DROP logs delete operations.

So, is it safe to assume that if I want to get rid of a relatively large table and I want this to happen as QUICKLY and with as LITTLE logging overhead as possible I should TRUNCATE TABLE before I DROP TABLE? Does doing this in RECOVERY SIMPLE make any difference?

I should note that this needs to happen in an automated fashion (within pre-written scripts) because this will be deployed to client databases where both downtime and log file growth could be a problem.

Answer 1

While TRUNCATE doesn't log individual rows, it does log for the page/extent. This is why you can rollback a truncate (which not a lot of people know). My guess is that if you just truncate then drop it will actually be slower than a drop on its own. If you commit in between, maybe not, but it would also depend on the log activity, recovery model, when you hit a checkpoint, etc.

Why is the speed important here? It's not like users are using the table if you're about to drop it...

Why don't you test it? Unless someone has run an extensive study about this covering several different variables, I doubt you're going to get much more than quasi-educated guesses.