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.
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- What is the best way to cache a table from a (SQL)
- php PDO::FETCH_ASSOC doesnt detect select after ba
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.
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
DELETE VS TRUNCATE
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 logWHERE
clause inDELETE
but inTRUNCATE
you cannot use itDELETE
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 rowDELETE
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 thesepages will be deallocated quickly by a background cleanup process
TRUNCATE TABLE
removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remainDELETE
statement doesn'tRESEED
identity column butTRUNCATE
statementRESEEDS
theIDENTITY
columnTRUNCATE TABLE
on tables that:FOREIGN KEY
constraint. (You can truncate a table that has a foreign key that references itself.)TRUNCATE TABLE
cannot activate a trigger because the operation does not log individual row deletionsI think you means the difference between DELETE TABLE and TRUNCATE TABLE.
DROP TABLE
DELETE TABLE
TRUNCATE TABLE
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
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)