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

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


当前回答

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

begin Tran
delete from  Employee

select * from Employee
Rollback
select * from Employee

其他回答

它方便的一个重要原因是,当您需要刷新数百万行表中的数据,但又不想重新构建它时。“Delete *”会花费很长时间,而Truncate对性能的影响可以忽略不计。

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

DELETE语句可以有一个WHERE子句来删除特定的记录,而TRUNCATE语句不需要任何子句并擦除整个表。 重要的是,DELETE语句记录删除日期,而TRUNCATE语句不记录删除日期。

Truncate and Delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both Sql commands can create lot of trouble until you are familiar with details before using it. An Incorrect choice of command can result is either very slow process or can even blew up log segment, if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use truncate and delete command in SQL but before using these you should be aware of the Differences between Truncate and Delete, and based upon them, we should be able to find out when DELETE is better option for removing data or TRUNCATE should be used to purge tables.

参考资料请点击这里

都是很好的答案,我必须补充一句:

由于TRUNCATE TABLE是一个DDL(数据定义语言)命令,而不是DML(数据操作语言)命令,删除触发器不会运行。