[sql] Difference between drop table and truncate table?

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.

DELETE

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

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.

From: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

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

Drop drop whole table and all its structure

truncate delete all rows from table it is different from delete that it also delete indexes of rows

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 ['TRUNCATE TABLE' can be rolled back as well]. 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)

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

Delete Statement

Delete Statement delete table rows and return the number of rows is deleted from the table.in this statement, we use where clause to deleted data from the table

• Delete Statement is slower than Truncate statement because it deleted records one by one

Truncate Statement

Truncate statement Deleted or removing all the rows from the table.

• It is faster than the Delete Statement because it deleted all the records from the table
• Truncate statement not return the no of rows are deleted from the table

Drop statement

Drop statement deleted all records as well as the structure of the table

TRUNCATE 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.

DROP Table

DROP TABLE [table_name];

The DROP command is used to remove a table from the database. It is a DDL command. All the rows, indexes and privileges of the table will also be removed. DROP operation cannot be rolled back.

DELETE Table

DELETE FROM [table_name]
WHERE [condition];

DELETE FROM [table_name];

The DELETE command is a DML command. It can be used to delete all the rows or some rows from the table based on the condition specified in WHERE clause. It is executed using a row lock, each row in the table is locked for deletion. It maintain the transaction log, so it is slower than TRUNCATE. DELETE operations can be rolled back.

TRUNCATE Table

TRUNCATE TABLE [table_name];

The TRUNCATE command removes all rows from a table. It won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster than DELETE. It is executed using a table lock and whole table is locked for remove all records. It is a DDL command. TRUNCATE operations cannot be rolled back.

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.

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:
   1. Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
   2. Participate in an indexed view.
   3. 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

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.

DELETE TableA instead of TRUNCATE TableA? A common misconception is that they do the same thing. Not so. In fact, there are many differences between the two.

DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted.

You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

TRUNCATE will reset any identity columns to the default seed value.

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

DROP TABLE deletes the table.

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

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.

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.

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

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