In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them.
Many of my colleagues (almost all of whom are much more experienced than I am) typically do this:
TRUNCATE TABLE #mytemp
DROP TABLE #mytemp
I typically use a single DROP TABLE
in my scripts.
Is there any good reason for doing a TRUNCATE
immediately before a DROP
?
Best Answer
No.
TRUNCATE
andDROP
are almost identical in behavior and speed, so doing aTRUNCATE
right before aDROP
is simply unnecessary.Note: I wrote this answer from a SQL Server perspective and assumed it would apply equally to Sybase. It appears that this is not entirely the case.
Note: When I first posted this answer, there were several other highly rated answers -- including the then-accepted answer -- that made several false claims like:
TRUNCATE
is not logged;TRUNCATE
cannot be rolled back;TRUNCATE
is faster thanDROP
; etc.Now that this thread has been cleaned up, the rebuttals that follow may seem tangential to the original question. I leave them here as a reference for others looking to debunk these myths.
There are a couple of popular falsehoods -- pervasive even among experienced DBAs -- that may have motivated this
TRUNCATE-then-DROP
pattern. They are:TRUNCATE
is not logged, therefore it cannot be rolled back.TRUNCATE
is faster thanDROP
.Let me rebut these falsehoods. I am writing this rebuttal from a SQL Server perspective, but everything I say here should be equally applicable to Sybase.
TRUNCATE is logged, and it can be rolled back.
TRUNCATE
is a logged operation, so it can be rolled back. Just wrap it in a transaction.Note, however, that this is not true for Oracle. Though logged and protected by Oracle's undo and redo functionality,
TRUNCATE
and other DDL statements can't be rolled back by the user because Oracle issues implicit commits immediately before and after all DDL statements.TRUNCATE
is minimally logged, as opposed to fully logged. What does that mean? Say youTRUNCATE
a table. Instead of putting each deleted row in the transaction log,TRUNCATE
just marks the data pages they live on as unallocated. That's why it's so fast. That's also why you cannot recover the rows of aTRUNCATE
-ed table from the transaction log using a log reader. All you'll find there are references to the deallocated data pages.Compare this to
DELETE
. If youDELETE
all the rows in a table and commit the transaction you can still, in theory, find the deleted rows in the transaction log and recover them from there. That's becauseDELETE
writes every deleted row to the transaction log. For large tables, this will make it much slower thanTRUNCATE
.DROP is just as fast as TRUNCATE.
TRUNCATE
,DROP
is a minimally logged operation. That meansDROP
can be rolled back too. That also means it works exactly the same way asTRUNCATE
. Instead of deleting individual rows,DROP
marks the appropriate data pages as unallocated and additionally marks the table's metadata as deleted.Because
TRUNCATE
andDROP
work exactly the same way, they run just as fast as one another. There is no point toTRUNCATE
-ing a table beforeDROP
-ing it. Run this demo script on your development instance if you don't believe me.On my local machine with a warm cache, the results I get are as follows:
So, for a 134 million row table both
DROP
andTRUNCATE
take effectively no time at all. (On a cold cache they take about 2-3 seconds for the first run or two.) I also believe that the higher average duration for theTRUNCATE
thenDROP
operation is attributable to load variations on my local machine and not because the combination is somehow magically an order of magnitude worse than the individual operations. They are, after all, almost exactly the same thing.If you're interested in more detail about the logging overhead of these operations, Martin has a straightforward explanation of that.