T-SQL Tutorial

T-SQL Transaction Statements


A T-SQL transaction is a single logical unit of work and it is composed of several SQL Server statements. The transaction begins with the first SQL Server statement executed and ends when the transaction is saved or rolled back.

BEGIN Distributed Transaction

The T-SQL statement BEGIN DISTRIBUTED TRANSACTION specifies the start of a distributed transaction.

BEGIN DISTRIBUTED { TRAN | TRANSACTION }
[ transaction_name | @transaction_name_variable ] ;


BEGIN Transaction

The T-SQL statement BEGIN TRANSACTION specifies the start of a local transaction.

BEGIN TRANSACTION transaction_name;

COMMIT Transaction

The T-SQL statement COMMIT TRANSACTION specifies the end of a successful transaction.

COMMIT TRANSACTION transaction_name;

COMMIT Work

The T-SQL statement COMMIT WORK specifies the end of a transaction.

COMMIT WORK;

ROLLBACK Transaction

The T-SQL statement ROLLBACK TRANSACTION rollback a transaction to the start of the transaction, or to a savepoint inside the transaction.

ROLLBACK TRANSACTION transaction_name;

ROLLBACK Work

The T-SQL statement ROLLBACK WORK specifies the rollback of a user specified transaction.

ROLLBACK WORK;

SAVE Transaction

The T-SQL statement SAVE TRANSACTION is used to create a savepoint within a transaction.

SAVE TRANSACTION savepoint_name;

Transaction Example

USE model;
GO
CREATE TABLE myTestTable (id INT, name VARCHAR(100));
GO
DECLARE @TransactionName VARCHAR(50) = 'myTransaction';
BEGIN TRANSACTION @TransactionName
INSERT INTO myTestTable(id, name) VALUES (1, 'Test_1');
ROLLBACK TRANSACTION @TransactionName;
INSERT INTO myTestTable(id, name) VALUES (2, 'Test_2');
GO
SELECT * FROM myTestTable;
GO
DROP TABLE myTestTable;
GO