如果不允许回滚 TRUNCATE 语句，那么如何在事务中使用它? [英] If it is not allowed to rollback a TRUNCATE statement then how is it possible to use it in a transaction?

问题描述

可能的重复:
在事务中截断表

如果不允许回滚 TRUNCATE 语句，那么它如何在事务中回滚?考虑以下代码:

If it is not allowed to rollback a TRUNCATE statement then how is it rolled back in a transaction? Consider the following code:

USE tempdb
GO
-- Create Test Table
CREATE TABLE TruncateTest (ID INT)
INSERT INTO TruncateTest (ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
GO
-- Check the data before truncate
SELECT * FROM TruncateTest
GO
-- Begin Transaction
BEGIN TRAN
-- Truncate Table
TRUNCATE TABLE TruncateTest
GO
-- Check the data after truncate
SELECT * FROM TruncateTest
GO
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check the data after Rollback
SELECT * FROM TruncateTest
GO
-- Clean up
DROP TABLE TruncateTest
GO

推荐答案

SQL Server 普遍认为 截断无法回滚，因为它没有被记录.但是，您问题中的代码只是验证这实际上是一个神话.

It is a fairly common SQL Server belief that Truncate Cannot Be Rolled Back Because It Is Not Logged. However the code in your question just verifies that this is in fact a myth.

TRUNCATE 确实比 DELETE 需要更少的日志记录，因为它只记录页面释放和一些系统表的元数据更新，而不是记录删除行的全部内容到日志 (查看详细分类在这里记录).

TRUNCATE does entail much less logging than DELETE as it only logs the page deallocatons and some metadata updates to system tables rather than recording the entire contents of the deleted rows to the log (See a detailed breakdown of logging here).

SQL Server 继续锁定解除分配的页面，直到事务提交以防止它们重新分配，因此 TRUNCATE 的 ROLLBACK 只需要反转这些页面释放和系统表更新记录在日志中.

SQL Server continues to lock the deallocated pages until the transaction commits preventing their re-allocation so ROLLBACK of a TRUNCATE simply needs to reverse these page deallocations and system table updates that are recorded in the log.

这篇关于如果不允许回滚 TRUNCATE 语句，那么如何在事务中使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！