优点 &TRUNCATE 与 DELETE FROM 的缺点 [英] Pros & Cons of TRUNCATE vs DELETE FROM

问题描述

有人可以快速概述一下使用以下两个语句的优缺点:

Could someone give me a quick overview of the pros and cons of using the following two statements:

TRUNCATE TABLE dbo.MyTable

对比

DELETE FROM dbo.MyTable

当一切都说完后，他们似乎都在做同样的事情；但两者之间一定有区别吗.

It seems like they both do the same thing when all is said and done; but are there must be differences between the two.

推荐答案

TRUNCATE 不生成任何回滚数据，这使得它快如闪电.它只是释放表使用的数据页.

TRUNCATE doesn't generate any rollback data, which makes it lightning fast. It just deallocates the data pages used by the table.

但是，如果您在事务中并希望能够撤消"此删除，则需要使用 DELETE FROM，它提供了回滚的能力.

However, if you are in a transaction and want the ability to "undo" this delete, you need to use DELETE FROM, which gives the ability to rollback.

请注意，以上对于 SQL Server 是不正确的(但它确实适用于 Oracle).在 SQL Server 中，如果您在事务中并且该事务尚未提交，则可以回滚截断操作.从 SQL Server 的角度来看，DELETE FROM 和 TRUNCATE 之间的一个主要区别是这个:DELETE 语句一次删除一行，并在事务日志中为每个删除的行记录一个条目.TRUNCATE TABLE 通过取消分配用于存储表数据的数据页来删除数据，并仅记录事务日志中的页取消分配."

Note that the above is incorrect for SQL Server (but it does apply to Oracle). In SQL Server, it is possible to rollback a truncate operation if you are inside a transaction and the transaction has not been committed. From a SQL Server perspective, one key difference between DELETE FROM and TRUNCATE is this: "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."

换句话说，TRUNCATE 期间的日志记录较少，因为事务日志中只记录了页面释放，而 DELETE FROM 记录了每一行删除.这就是 TRUNCATE 闪电般快速的原因之一.

In other words, there is less logging during a TRUNCATE because only the page deallocations are recorded in the transaction log, whereas with a DELETE FROM each row deletion is recorded. That's one of the reasons TRUNCATE is lightning fast.

还请注意，该 MSDN 链接不能截断由外键约束引用、参与索引视图或使用事务复制或合并复制发布的表.

Note also from that MSDN link that you cannot truncate tables that are referenced by foreign key constraints, participate in an indexed view, or are published by using transactional replication or merge replication.

编辑 2:另一个关键点是 TRUNCATE TABLE 会将您的身份重置为初始种子，而 DELETE FROM 将从停止的地方继续递增.参考:Ben Robinson 的回答.

EDIT 2: Another key point is that TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off. Reference: Ben Robinson's answer.

这篇关于优点 &TRUNCATE 与 DELETE FROM 的缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！