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

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


当前回答

“Truncate不会记录任何东西”是正确的。我想更进一步:

Truncate不会在事务的上下文中执行。

截断比删除的速度优势应该是明显的。根据你的情况,这种优势从微不足道到巨大不等。

然而,我看到截断无意中破坏了引用完整性,并违反了其他约束。通过在事务外部修改数据而获得的权力,必须与在没有安全网的情况下走钢丝时继承的责任相平衡。

其他回答

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语句,您将指示SQL Server删除表中的每条记录,而不进行任何日志记录或事务处理。

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

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

你不能用truncate这样做。

TRUNCATE是快的,DELETE是慢的。

尽管TRUNCATE没有问责制。