Home » Why use both TRUNCATE and DROP?

Why use both TRUNCATE and DROP?

Solutons:


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.

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

OK thought I’d attempt to do some benchmarks that didn’t rely on any “warm cacheing” so that hopefully they’d be a more realistic test (also using Postgres, to see if it matches the same characteristics of other posted answers):

My benchmarks using postgres 9.3.4 with a large-ish database, (hopefully large enough to not fit in RAM cache):

Using this test DB script: https://gist.github.com/rdp/8af84fbb54a430df8fc0

with 10M rows:

truncate: 1763ms
drop: 2091ms
truncate + drop: 1763ms (truncate) + 300ms (drop) (2063ms total)
drop + recreate: 2063ms (drop) + 242ms (recreate)

with 100M rows:

truncate: 5516ms
truncate + drop: 5592ms
drop: 5680ms (basically, the exact same ballpark)

So from this I surmise the following: drop is “about” as fast (or faster) as truncate+drop (at least for modern versions of Postgres), however, if you plan on also turning around and recreating the table, you may as well stick with doing a straight truncate, which is faster than a drop+recreate (makes sense). FWIW.

note 1: https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886 (says that postgres 9.2 may have a faster truncate than previous versions). As always, benchmark with your own system to see its characteristics.

note 2: truncate can be rolled back in postgres, if in a transaction:
http://www.postgresql.org/docs/8.4/static/sql-truncate.html

note 3: truncate can, with small tables, sometimes be slower than a delete: https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886

Related Solutions

Extract file from docker image?

You can extract files from an image with the following commands: docker create $image # returns container ID docker cp $container_id:$source_path $destination_path docker rm $container_id According to the docker create documentation, this doesn't run the...

Transfer files using scp: permission denied

Your commands are trying to put the new Document to the root (/) of your machine. What you want to do is to transfer them to your home directory (since you have no permissions to write to /). If path to your home is something like /home/erez try the following:...

What’s the purpose of DH Parameters?

What exactly is the purpose of these DH Parameters? These parameters define how OpenSSL performs the Diffie-Hellman (DH) key-exchange. As you stated correctly they include a field prime p and a generator g. The purpose of the availability to customize these...

How to rsync multiple source folders

You can pass multiple source arguments. rsync -a /etc/fstab /home/user/download bkp This creates bkp/fstab and bkp/download, like the separate commands you gave. It may be desirable to preserve the source structure instead. To do this, use / as the source and...

Benefits of Structured Logging vs basic logging

There are two fundamental advances with the structured approach that can't be emulated using text logs without (sometimes extreme levels of) additional effort. Event Types When you write two events with log4net like: log.Debug("Disk quota {0} exceeded by user...

Interfaces vs Types in TypeScript

2019 Update The current answers and the official documentation are outdated. And for those new to TypeScript, the terminology used isn't clear without examples. Below is a list of up-to-date differences. 1. Objects / Functions Both can be used to describe the...

Get total as you type with added column (append) using jQuery

One issue if that the newly-added column id's are missing the id number. If you look at the id, it only shows "price-", when it should probably be "price-2-1", since the original ones are "price-1", and the original ones should probably be something like...

Determining if a file is a hard link or symbolic link?

Jim's answer explains how to test for a symlink: by using test's -L test. But testing for a "hard link" is, well, strictly speaking not what you want. Hard links work because of how Unix handles files: each file is represented by a single inode. Then a single...

How to restrict a Google search to results of a specific language?

You can do that using the advanced search options: http://www.googleguide.com/sharpening_queries.html I also found this, which might work for you: http://www.searchenginejournal.com/how-to-see-google-search-results-for-other-locations/25203/ Just wanted to add...

Random map generation

Among the many other related questions on the site, there's an often linked article for map generation: Polygonal Map Generation for Games you can glean some good strategies from that article, but it can't really be used as is. While not a tutorial, there's an...

How to prettyprint a JSON file?

The json module already implements some basic pretty printing in the dump and dumps functions, with the indent parameter that specifies how many spaces to indent by: >>> import json >>> >>> your_json = '["foo", {"bar":["baz", null,...

How can I avoid the battery charging when connected via USB?

I have an Android 4.0.3 phone without root access so can't test any of this but let me point you to /sys/class/power_supply/battery/ which gives some info/control over charging issues. In particular there is charging_enabled which gives the current state (0 not...

How to transform given dataset in python? [closed]

From your expected result, it appears that each "group" is based on contiguous id values. For this, you can use the compare-cumsum-groupby pattern, and then use agg to get the min and max values. # Sample data. df = pd.DataFrame( {'id': [1, 2, 2, 2, 2, 2, 1, 1,...

Output of the following C++ Program [closed]

It works exactly like this non-recursive translation: int func_0() { return 2; } int func_1() { return 3; } int func_2() { return func_1() + func_0(); } // Returns 3 + 2 = 5 int func_3() { return func_2() + func_1(); } // Returns 5 + 3 = 8 int func_4() { return...

Making a circle out of . (periods) [closed]

Here's the maths and even an example program in C: http://pixwiki.bafsoft.com/mags/5/articles/circle/sincos.htm (link no longer exists). And position: absolute, left and top will let you draw: http://www.w3.org/TR/CSS2/visuren.html#choose-position Any further...

Should I use a code converter (Python to C++)?

Generally it's an awful way to write code, and does not guarantee that it will be any faster. Things which are simple and fast in one language can be complex and slow in another. You're better off either learning how to write fast Python code or learning C++...

tkinter: cannot concatenate ‘str’ and ‘float’ objects

This one line is more than enough to cause the problem: text="რეგულარი >> "+2.23+ 'GEL' 2.23 is a floating-point value; 'GEL' is a string. What does it mean to add an arithmetic value and a string of letters? If you want the string label 'რეგულარი...