Transact-SQL Implicit Transactions
SQL Server 2005
DB-Library applications and Transact-SQL scripts use the Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement to start implicit transaction mode. Use the SET IMPLICIT_TRANSACTIONS OFF statement to turn implicit transaction mode off. Use the COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to end each transaction.
SET QUOTED_IDENTIFIER OFF; GO SET NOCOUNT OFF; GO USE AdventureWorks; GO CREATE TABLE ImplicitTran (Cola int PRIMARY KEY, Colb char(3) NOT NULL); GO SET IMPLICIT_TRANSACTIONS ON; GO -- First implicit transaction started by an INSERT statement. INSERT INTO ImplicitTran VALUES (1, 'aaa'); GO INSERT INTO ImplicitTran VALUES (2, 'bbb'); GO -- Commit first transaction. COMMIT TRANSACTION; GO -- Second implicit transaction started by a SELECT statement. SELECT COUNT(*) FROM ImplicitTran; GO INSERT INTO ImplicitTran VALUES (3, 'ccc'); GO SELECT * FROM ImplicitTran; GO -- Commit second transaction. COMMIT TRANSACTION; GO SET IMPLICIT_TRANSACTIONS OFF; GO