Why use both TRUNCATE and DROP?

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?

No.

TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP 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 than DROP; 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:

• Myth: TRUNCATE is not logged, therefore it cannot be rolled back.

• Myth: TRUNCATE is faster than DROP.

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.

USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE truncate_demo (
    whatever    VARCHAR(10)
);

INSERT INTO truncate_demo (whatever)
VALUES ('log this');

BEGIN TRANSACTION;
    TRUNCATE TABLE truncate_demo;
ROLLBACK TRANSACTION;

SELECT *
FROM truncate_demo;

DROP TABLE truncate_demo;

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 you TRUNCATE 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 a TRUNCATE-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 you DELETE 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 because DELETE writes every deleted row to the transaction log. For large tables, this will make it much slower than TRUNCATE.

DROP is just as fast as TRUNCATE.

• Like TRUNCATE, DROP is a minimally logged operation. That means DROP can be rolled back too. That also means it works exactly the same way as TRUNCATE. Instead of deleting individual rows, DROP marks the appropriate data pages as unallocated and additionally marks the table's metadata as deleted.

-
Because TRUNCATE and DROP work exactly the same way, they run just as fast as one another. There is no point to TRUNCATE-ing a table before DROP-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:

table row count: 134,217,728

run#        transaction duration (ms)
      TRUNCATE   TRUNCATE then DROP   DROP
==========================================
01       0               1             4
02       0              39             1
03       0               1             1
04       0               2             1
05       0               1             1
06       0              25             1
07       0               1             1
08       0               1             1
09       0               1             1
10       0              12             1
------------------------------------------
avg      0              8.4           1.3

So, for a 134 million row table both DROP and TRUNCATE 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 the TRUNCATE then DROP 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.

StackExchange Database Administrators Q#4163, answer score: 145