Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jul 27 '11 at 23:08

This question came from our site for professional and enthusiast programmers.

    
Have to agree with IAbstract. Although truncate before drop seems useless. –  cularis Jul 27 '11 at 17:40
    
As Bohemian said, the practice is strictly to improve performance of the drop operation. –  yrushka Jul 28 '11 at 9:47
4  
why did you accept a **wrong"" answer –  gbn Nov 9 '11 at 5:36
3  
@gbn - At the time this was asked all the answers were wrong. The asker just accepted the highest voted answer, which was at +16 at the time. –  Nick Chammas Nov 9 '11 at 6:25
5  
Very interesting that this question got picked up and corrected like this. +1 for stack exchange.. But -1 for so many votes from people who actually had no idea. HRMMM –  user606723 Nov 9 '11 at 14:30
show 4 more comments

3 Answers

up vote 73 down vote accepted
+250

No.

TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary.


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.

share|improve this answer
5  
+1; I did not know that TRUNCATE could be rolled back. I can always count on Stack Exchange to humble me if I ever start to think I know what I'm doing. –  Jon of All Trades May 17 '12 at 18:47
    
Awesome answer Nick. I have a question for you what is the time frame that these pages can be rolled back? I have to imagine at some point these pages get relocated to new items. –  Zane Dec 13 '12 at 13:29
    
@Zane - TRUNCATE is just another DDL statement, so you can recover any pages it touches in the same situations where you can undo other SQL Server DML or DDL commands: e.g. you're inside an active transaction, or your database uses the full recovery model, etc. If you're talking about recovering the pages via direct inspection (e.g. using DBCC or some third-party tool), then that depends on the level of write activity your database is handling. As you said, at some point SQL Server will reallocate the pages freed by TRUNCATE for use. –  Nick Chammas Dec 15 '12 at 12:52
add comment

Testing TRUNCATE then DROP vs just doing the DROP directly shows that the first approach actually has a slight increased logging overhead so may even be mildly counter productive.

Looking at the individual log records shows the TRUNCATE ... DROP version is almost identical to the DROP version except has these additional entries.

+-----------------+---------------+-------------------------+
|    Operation    |    Context    |      AllocUnitName      |
+-----------------+---------------+-------------------------+
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust    |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst      |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst      |
| LOP_HOBT_DDL    | LCX_NULL      | NULL                    |
| LOP_MODIFY_ROW  | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_HOBT_DDL    | LCX_NULL      | NULL                    |
| LOP_MODIFY_ROW  | LCX_CLUSTERED | sys.sysrowsets.clust    |
| LOP_LOCK_XACT   | LCX_NULL      | NULL                    |
+-----------------+---------------+-------------------------+

So the TRUNCATE first version ends up wasting a little bit of effort doing some updates to various system tables as follows

  • Update rcmodified for all table columns in sys.sysrscols
  • Update rcrows in sysrowsets
  • Zero out pgfirst,pgroot,pgfirstiam,pcused,pcdata,pcreserved in sys.sysallocunits

These system table rows only end up getting deleted when the table is dropped in the next statement.

A full break down of the logging carried out by TRUNCATE vs DROP is below. I've also added DELETE in for comparison purposes.

+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
|                   |                   |                    |                            Bytes                           |                            Count                           |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Operation         | Context           | AllocUnitName      | Truncate / Drop  | Drop Only | Truncate Only | Delete Only | Truncate / Drop  | Drop Only | Truncate Only | Delete Only |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| LOP_BEGIN_XACT    | LCX_NULL          |                    | 132              | 132       | 132           | 132         | 1                | 1         | 1             | 1           |
| LOP_COMMIT_XACT   | LCX_NULL          |                    | 52               | 52        | 52            | 52          | 1                | 1         | 1             | 1           |
| LOP_COUNT_DELTA   | LCX_CLUSTERED     | System Table       | 832              |           | 832           |             | 4                |           | 4             |             |
| LOP_DELETE_ROWS   | LCX_MARK_AS_GHOST | System Table       | 2864             | 2864      |               |             | 22               | 22        |               |             |
| LOP_DELETE_ROWS   | LCX_MARK_AS_GHOST | T                  |                  |           |               | 8108000     |                  |           |               | 1000        |
| LOP_HOBT_DDL      | LCX_NULL          |                    | 108              | 36        | 72            |             | 3                | 1         | 2             |             |
| LOP_LOCK_XACT     | LCX_NULL          |                    | 336              | 296       | 40            |             | 8                | 7         | 1             |             |
| LOP_MODIFY_HEADER | LCX_PFS           | Unknown Alloc Unit | 76               | 76        |               | 76          | 1                | 1         |               | 1           |
| LOP_MODIFY_ROW    | LCX_CLUSTERED     | System Table       | 644              | 348       | 296           |             | 5                | 3         | 2             |             |
| LOP_MODIFY_ROW    | LCX_IAM           | T                  | 800              | 800       | 800           |             | 8                | 8         | 8             |             |
| LOP_MODIFY_ROW    | LCX_PFS           | T                  | 11736            | 11736     | 11736         |             | 133              | 133       | 133           |             |
| LOP_MODIFY_ROW    | LCX_PFS           | Unknown Alloc Unit | 92               | 92        | 92            |             | 1                | 1         | 1             |             |
| LOP_SET_BITS      | LCX_GAM           | T                  | 9000             | 9000      | 9000          |             | 125              | 125       | 125           |             |
| LOP_SET_BITS      | LCX_IAM           | T                  | 9000             | 9000      | 9000          |             | 125              | 125       | 125           |             |
| LOP_SET_BITS      | LCX_PFS           | System Table       | 896              | 896       |               |             | 16               | 16        |               |             |
| LOP_SET_BITS      | LCX_PFS           | T                  |                  |           |               | 56000       |                  |           |               | 1000        |
| LOP_SET_BITS      | LCX_SGAM          | Unknown Alloc Unit | 168              | 224       | 168           |             | 3                | 4         | 3             |             |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Total             |                   |                    | 36736            | 35552     | 32220         | 8164260     | 456              | 448       | 406           | 2003        |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+

The test was carried out in a database with full recovery model against a 1,000 row table with one row per page. The table consumes 1,004 pages in total due to the root index page and 3 intermediate level index pages.

8 of these pages are single page allocations in mixed extents with the remainder distributed across 125 Uniform Extents. The 8 single page de-allocations show up as the 8 LOP_MODIFY_ROW,LCX_IAM log entries. The 125 extent deallocations as LOP_SET_BITS LCX_GAM,LCX_IAM. Both of these operations also require an update to the associated PFS page hence the combined 133 LOP_MODIFY_ROW, LCX_PFS entries. Then when the table is actually dropped the metadata about it needs to be removed from various system tables hence the 22 system table LOP_DELETE_ROWS log entries (accounted for as below)

+----------------------+--------------+-------------------+-------------------+
|        Object        | Rows Deleted | Number of Indexes | Delete Operations |
+----------------------+--------------+-------------------+-------------------+
| sys.sysallocunits    |            1 |                 2 |                 2 |
| sys.syscolpars       |            2 |                 2 |                 4 |
| sys.sysidxstats      |            1 |                 2 |                 2 |
| sys.sysiscols        |            1 |                 2 |                 2 |
| sys.sysobjvalues     |            1 |                 1 |                 1 |
| sys.sysrowsets       |            1 |                 1 |                 1 |
| sys.sysrscols        |            2 |                 1 |                 2 |
| sys.sysschobjs       |            2 |                 4 |                 8 |
+----------------------+--------------+-------------------+-------------------+
|                      |              |                   |                22 |
+----------------------+--------------+-------------------+-------------------+

Full Script Below

DECLARE @Results TABLE
(
    Testing int NOT NULL,
    Operation nvarchar(31) NOT NULL,
    Context nvarchar(31)  NULL,
    AllocUnitName nvarchar(1000) NULL,
    SumLen int NULL,
    Cnt int NULL
)

DECLARE @I INT = 1

WHILE @I <= 4
BEGIN
IF OBJECT_ID('T','U') IS NULL
     CREATE TABLE T(N INT PRIMARY KEY,Filler char(8000) NULL)

INSERT INTO T(N)
SELECT DISTINCT TOP 1000 number
FROM master..spt_values


CHECKPOINT

DECLARE @allocation_unit_id BIGINT

SELECT @allocation_unit_id = allocation_unit_id
FROM   sys.partitions AS p
       INNER JOIN sys.allocation_units AS a
         ON p.hobt_id = a.container_id
WHERE  p.object_id = object_id('T')  

DECLARE @LSN NVARCHAR(25)
DECLARE @LSN_HEX NVARCHAR(25)

SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null)


SELECT @LSN_HEX=
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)

  BEGIN TRAN
    IF @I = 1
      BEGIN
          TRUNCATE TABLE T

          DROP TABLE T
      END
    ELSE
      IF @I = 2
        BEGIN
            DROP TABLE T
        END
      ELSE
        IF @I = 3
          BEGIN
              TRUNCATE TABLE T
          END  
      ELSE
        IF @I = 4
          BEGIN
              DELETE FROM T
          END                
  COMMIT

INSERT INTO @Results
SELECT @I,
       CASE
         WHEN GROUPING(Operation) = 1 THEN 'Total'
         ELSE Operation
       END,
       Context,
       CASE
         WHEN AllocUnitId = @allocation_unit_id THEN 'T'
         WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
         ELSE AllocUnitName
       END,
       COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
       COUNT(*)                              AS Cnt
FROM   fn_dblog(@LSN_HEX, null) AS D
WHERE  [Current LSN] > @LSN  
GROUP BY GROUPING SETS((Operation, Context,
       CASE
         WHEN AllocUnitId = @allocation_unit_id THEN 'T'
         WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
         ELSE AllocUnitName
       END),())


SET @I+=1
END 

SELECT Operation,
       Context,
       AllocUnitName,
       AVG(CASE WHEN Testing = 1 THEN SumLen END) AS [Truncate / Drop Bytes],
       AVG(CASE WHEN Testing = 2 THEN SumLen END) AS [Drop Bytes],
       AVG(CASE WHEN Testing = 3 THEN SumLen END) AS [Truncate Bytes],
       AVG(CASE WHEN Testing = 4 THEN SumLen END) AS [Delete Bytes],
       AVG(CASE WHEN Testing = 1 THEN Cnt END) AS [Truncate / Drop Count],
       AVG(CASE WHEN Testing = 2 THEN Cnt END) AS [Drop Count],
       AVG(CASE WHEN Testing = 3 THEN Cnt END) AS [Truncate Count],
       AVG(CASE WHEN Testing = 4 THEN Cnt END) AS [Delete Count]              
FROM   @Results
GROUP  BY Operation,
          Context,
          AllocUnitName   
ORDER BY Operation, Context,AllocUnitName        

DROP TABLE T
share|improve this answer
5  
Excellent stuff as always. –  Mark Storey-Smith Nov 9 '11 at 13:28
1  
Excellent indeed. I wanted to test this out myself (and I did ask @MarkStorey-Smith about where to look for transaction log space used) but I settled instead for this time-based test of TRUNCATE and DROP against a 137 million row table. (I linked to it at the end of my answer as well.) –  Nick Chammas Nov 9 '11 at 14:46
    
I would have voted Nick's answer, but it's over ambitious in pronouncing them equivalent - even though the difference is insignificant. So I'm using +1 here to contribute to making this go up. –  孔夫子 Oct 7 '12 at 8:58
2  
@RichardTheKiwi - Perhaps it looks overambitious now, but when I posted my answer 1) Martin hadn't chimed in yet and 2) all the answers at that time (about 4 of them, all highly upvoted, including the then-accepted answer, which was at +16) claimed that DROP and TRUNCATE were different enough to merit the pattern that the OP was asking about. –  Nick Chammas Dec 15 '12 at 12:32
add comment

The point of the truncate is to simply and irrevocably remove everything in the table (some technical specifics based on data store engines may differ slightly) -- skipping heavy logging, etc.

drop table logs all changes as the changes are being made. So, to have minimal logging and reduce useless system churn, I would suspect a very large table could be truncated first, then dropped.

share|improve this answer
    
And with a temp table, there is almost no reason to log it. Thanks –  user606723 Jul 28 '11 at 13:18
4  
If you wrap your TRUNCATE statement in a transaction it is revocable. –  Nick Chammas Nov 8 '11 at 21:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.