SQL Server/Rollback対象

　Microsoft SQL Serverでは、deleteだけじゃなく、truncate, drop tableもRollbackできる、というお話。

　例えば、こんなテーブルで、

CREATE TABLE [Table02](
	[id] [bigint] primary key,
	[value] [nvarchar](max) NULL
)

INSERT INTO [Table02] VALUES (1, 'いち')
INSERT INTO [Table02] VALUES (2, 'に')

　以下のクエリでは、全てRollbackされます。

begin tran;
delete from [Table02];

select count(*) from [Table02]; -- 0

--commit tran;
rollback tran;

select count(*) from [Table02]; -- 2

begin tran;
truncate table [Table02];

select count(*) from [Table02]; -- 0

--commit tran;
rollback tran;

select count(*) from [Table02]; -- 2

begin tran;
drop table [Table02];

SELECT count(*) FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[Table02]')
 AND type in (N'U') -- 0

--commit tran;
rollback tran;

SELECT count(*) FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[Table02]')
 AND type in (N'U') -- 1