Difference between drop table and truncate table?

Question

I have some tables that I build as a part of my report rollup. I don't need them afterwards at all. Someone mentioned to truncate them as it would be faster.

Answer 1

DROP TABLE deletes the table.

TRUNCATE TABLE empties it, but leaves its structure for future data.

Answer 2

truncate removes all the rows, but not the table itself, it is essentially equivalent to deleting with no where clause, but usually faster.

Answer 3

Drop gets rid of the table completely, removing the definition as well. Truncate empties the table but does not get rid of the definition.

Answer 4

Truncating the table empties the table. Dropping the table deletes it entirely. Either one will be fast, but dropping it will likely be faster (depending on your database engine).

If you don't need it anymore, drop it so it's not cluttering up your schema.

Answer 5

In the SQL standard, DROP table removes the table and the table schema - TRUNCATE removes all rows.

Edit: Actually, they belong to a different set of languages. I would say that TRUNCATE is a part of the Data Manipulation Language wheares DROP belongs to the set of the Data Definition Language

Answer 6

TRUCNATE TABLE keeps all of your old indexing and whatnot. DROP TABLE would, obviously, get rid of the table and require you to recreate it later.

Answer 7

I think you means the difference between DELETE TABLE and TRUNCATE TABLE.

DROP TABLE

remove the table from the database.

DELETE TABLE

without a condition delete all rows. If there are trigger and references then this will process for every row. Also a index will be modify if there one.

TRUNCATE TABLE

set the row count zero and without logging each row. That it is many faster as the other both.

Answer 8

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

If you actually want to remove the table definitions as well as the data, simply drop the tables.

See this MSDN article for more info

Answer 9

DROP and TRUNC do different things:

TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

DROP TABLE

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

As far as speed is concerned the difference should be small. And anyway if you don't need the table structure at all, certainly use DROP.

Answer 10

The answers here match up to the question, but I'm going to answer the question you didn't ask. "Should I use truncate or delete?" If you are removing all rows from a table, you'll typically want to truncate, since it's much much faster. Why is it much faster? At least in the case of Oracle, it resets the high water mark. This is basically a dereferencing of the data and allows the db to reuse it for something else.

Answer 11

None of these answer point out an important difference about these two operations. Drop table is an operation that can be rolled back. However, truncate cannot be rolled back. In this way dropping a very large table can be very expensive if there are many rows, because they all have to be recorded in a temporary space in case you decide to roll it back.

Usually, if I want to get rid of a large table, I will truncate it, then drop it. This way the data will be nixed without record, and the table can be dropped, and that drop will be very inexpensive because no data needs to be recorded.

It is important to point out though that truncate just deletes data, leaving the table, while drop will, in fact, delete the data and the table itself. (assuming foreign keys don't preclude such an action)

Answer 12

I have a correction for one of the statements above... "truncate cannot be rolled back"

Truncate can be rolled back. There are some cases when you can't do a truncate or drop table, such as when you have a foreign key reference. For a task such as monthly reporting, I'd probably just drop the table once I didn't need it anymore. If I was doing this rollup reporting more often then I'd probably keep the table instead and use truncate.

Hope this helps, here's some more info that you should find useful...

Please see the following article for more details: http://sqlblog.com/blogs/denis_gobo/archive/2007/06/13/1458.aspx

Also, for more details on delete vs. truncate, see this article: http://www.sql-server-performance.com/faq/delete_truncate_difference_p1.aspx

Thanks! Jeff

Answer 13

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE TABLE may not be used on tables participating in an indexed view.

From http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx

Answer 14

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

Answer 15

DELETE VS TRUNCATE

1) The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log 2) We can use "WHERE" clause in DELETE but in TRUNCATE you cannot use it 3) When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row 4) After a DELETE statement is executed, the table can still contain empty pages.If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these
pages will be deallocated quickly by a background cleanup process 5) TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain 6) DELETE statement doesn't RESEED identity column but TRUNCATE statement RESEEDS the IDENTITY column 7) You cannot use TRUNCATE TABLE on tables that: a) Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.) b) Participate in an indexed view. c) Are published by using transactional replication or merge replication 8) TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions

Answer 16

Hi, What about the space allocated, does both commands behave same? I had read some article way back in 2000 which states "Truncate" also removes the table space. I am not sure why it is not true any more.

Sanjay