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 only takes a minute to sign up.
Sign up to join this community
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?
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:
TRUNCATE is not logged, therefore it cannot be rolled back.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 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.
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
rcmodified for all table columns in sys.sysrscolsrcrows in sysrowsetspgfirst,pgroot,pgfirstiam,pcused,pcdata,pcreserved in sys.sysallocunitsThese 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
Adding some historical perspective ...
Dropping a table requires updating several system tables, which in turn typically requires making these system table changes in a single transaction (think "begin tran, delete syscolumns, delete sysobjects, commit").
Also included in the 'drop table' is the need to deallocate all data/index pages associated with the table.
Many, many, many years ago ... the space deallocation process was included in the transaction that also updated the system tables;net result was that the larger the number of allocated pages, the longer it took to deallocate said pages, the longer the transaction (on the system tables) was left open, and thus a greater chance of blocking (on system tables) other processes trying to create/drop tables in tempdb (especially nasty with the older allpages==page-level locking and potential for table-level lock escalation).
One early method used (way back then) to reduce contention on the system tables was to reduce the time the locks were held on the system tables, and one (relatively) easy way to do this was to deallocate the data/index pages before dropping the table.
While truncate table doesn't deallocate all data/index pages, it does deallocate all but one 8-page (data) extent; another 'hack' was to then drop all indexes before dropping the table (yeah, separate txn on sysindexes but a smaller txn for drop table).
When you consider that (again, many, many years ago) there was just the single 'tempdb' database, and some applications made HEAVY use of that single 'tempdb' database, any 'hacks' that could reduce contention on the system tables in 'tempdb' were of benefit; over time things have improved ... multiple temporary databases, row-level locking on system tables, better deallocation methods, etc.
In the meantime the use of the truncate table doesn't hurt anything if left in the code.
There is maybe one historical and temporary reason. Before Postgres 9.3 EVENTS TRIGGER doesn't exists and you couldn't "ON DROP" TRIGGER.
But from Postgres 8.4 it's possible to do TRIGGER ON TRUNCATE
So maybe some people that would do some special thing have used the TRIGGER ON TRUNCATE to simulate the ON DROP and to ensure the activation of the trigger they do TRUNCATE and after DROP.
It makes sense to do TRUNCATE for tables that have foreign keys. However, for temporary tables just DROP is enough
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.
truncate may be wrapped in a transaction (which would be the safest option) which, of course, will allow you to roll back the operation.
