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