Difference between drop table and truncate table?

2019-01-30 09:51发布

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.

19条回答
三岁会撩人
2楼-- · 2019-01-30 10:38

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.

查看更多
爷、活的狠高调
3楼-- · 2019-01-30 10:39

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

查看更多
Summer. ? 凉城
4楼-- · 2019-01-30 10:41

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 star\"
5楼-- · 2019-01-30 10:42

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.

查看更多
ら.Afraid
6楼-- · 2019-01-30 10:43

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

查看更多
Summer. ? 凉城
7楼-- · 2019-01-30 10:44

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)

查看更多
登录 后发表回答