SQL中TRUNCATE和DELETE的区别是什么?

如果你的答案是针对特定平台的,请注明。


当前回答

是的,DELETE比较慢,TRUNCATE比较快。为什么?

DELETE必须读取记录,检查约束,更新块,更新索引,并生成重做/撤销。所有这些都需要时间。

TRUNCATE只是在数据库中为表(High Water Mark)调整一个指针,然后!数据不见了。

这是Oracle特有的,AFAIK。

其他回答

微软sql server的另一个不同之处在于,使用delete,你可以使用output语句来跟踪哪些记录被删除了,例如:

delete from [SomeTable]
output deleted.Id, deleted.Name

你不能用truncate这样做。

最大的区别是truncate是不记录日志的操作,而delete是。

简单地说,这意味着在数据库崩溃的情况下,不能通过截断恢复所操作的数据,但可以通过删除恢复。

详情请点击这里

在这个例子中,Truncate也可以被rollback

begin Tran
delete from  Employee

select * from Employee
Rollback
select * from Employee

DROP The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE. Table level lock will be added when Truncating. 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. Note that this operation will cause all DELETE triggers on the table to fire. Row level lock will be added when deleting.

来自:http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Truncate命令用于重新初始化表,它是一个删除表中所有行的DDL命令。DELETE是一个DML命令,用于根据某些条件删除一行或一组行,如果没有指定condition,则该命令将删除表中的所有行。