sql - TRUNCATE TABLE 会增加事务日志吗？

Question

我读过 Sql 中 DELETE 和 TRUNCATE TABLE 之间的区别之一是 TRUNCATE 操作 无法回滚 并且不会触发任何触发器( as written in this site for example ):

问题 :
这是否意味着当我 TRUNcATE TABLE 包含数百万条记录时，我不应该影响事务日志文件 - 即事务日志文件不应该在截断时增长 - 我是否正确？

Answer 1

在 MS SQL Server 中(联机丛书)

Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

• Less transaction log space is used.

  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.

• Fewer locks are typically used.

  When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

• Without exception, zero pages are left in the table.

  After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. 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 these pages 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 remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

  If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

发件人: http://msdn.microsoft.com/en-us/library/ms177570.aspx
原问题:
从技术上讲，TRUNCATE 是从表中释放数据页，有效地从中删除所有记录。这个 Action 理论上可以回滚，直到没有数据页被重用。释放页面上的信息不会被删除，它们在数据文件中仍然可用。数据文件中的这些解除分配的页面可以重新使用(例如分配到另一个表)，并且可以覆盖它们上的数据。
事务日志包含在 TRUNCATE 期间从表中释放的页面列表(在 SQL Server 中)，但此列表比所有记录的列表短得多，因此事务日志不会增长到相同的程度。
根据不同 RDBMS 上的事务和 TRUNCATE 的实现，可以在事务内进行回滚。有时，如果页面上的数据仍然完整并且所有信息都可用，则可以在事务提交后进行“回滚”(恢复表)，但这是一些黑魔法，通常 RDBMS 不直接支持.