CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Justin Swartsel
Justin Swartsel

Reputation: 3431

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

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.

Upvotes: 8

Views: 3302

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280580

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.

Upvotes: 10

Oscar Gomez
Oscar Gomez

Reputation: 18488

In my experience truncate is way faster than drop, specially for large data sets.

Upvotes: 2

Related Questions