transaction examples

ALTER DATABASE TEST
SET RECOVERY SIMPLE;

use TEST;

CREATE TABLE dbo.Table1
(ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 detail varchar(10))

--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

INSERT INTO dbo.Table1 VALUES ('test1');
INSERT INTO dbo.Table1 VALUES ('test2');

DBCC LOG (TEST, 0);

--Clear the table
TRUNCATE TABLE dbo.Table1;


--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

SET IMPLICIT_TRANSACTIONS ON;
INSERT INTO dbo.Table1 VALUES ('test1');
INSERT INTO dbo.Table1 VALUES ('test2');
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF;

DBCC LOG (TEST, 0);

--Explicit Transaction
TRUNCATE TABLE dbo.Table1;


--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

BEGIN TRAN
INSERT INTO dbo.Table1 VALUES ('test1');
INSERT INTO dbo.Table1 VALUES ('test2');
COMMIT TRAN;

DBCC LOG (TEST, 0);


BEGIN TRAN
SAVE TRAN POINT1
TRUNCATE TABLE TABLE1
ROLLBACK TRAN  POINT1
COMMIT TRAN

SELECT * FROM TABLE1

BEGIN TRAN
SAVE TRAN POINT1
TRUNCATE TABLE TABLE1
COMMIT TRAN


insert into Table1
values('Table2')

save tran point1

insert into Table1
values('Table3')

save tran point1

insert into Table1
values('Table4')