Deshabilitar el modo autocommit de transacciones en SQL Server

Hay 3 modos básicos de transacciones con los cuales trabaja SQL Server. Estos modos son:

1. AutoCommit: Cada comando SQL/T-SQL tiene su propio espacio de transacción y automáticamente se hace la confirmación (COMMIT) cuando el comando termina.
2. Explicit: En este modo se indica el inicio y el final de una transacción de manera programática. Utilizando los comandos: BEGIN TRAN , COMMIT TRAN y ROLLBACK TRAN. Este modo es comúnmente utilizado en store procedures o scripts.
3. Implicit: En este modo, cuando emites ciertos comandos SQL Server automáticamente empieza una transacción. Entonces debes de confirmar o deshacer la transacción explícitamente utilizando los comandos COMMIT/ROLLBACK.

Autocommit es el modo predeterminado en SQL Server para las transacciones. Aunque este modo puede ser cómodo para el programador ya que no se tienen que escribir los comandos implicitos para abrir y cerrar una transacción, esto resulta peligro en ciertas ocasiones sobre todo si los datos con los que estamos trabajando se encuentran en un ambiente productivo.

Una manera de disminuir ese riego es deshabilitar el modo autocommit.

Esto se logra habilitando el modo de transacciones implícitas con el comando:

SET IMPLICIT TRANSACTIONS ON

Mostraré un ejemplo, habilitaré el modo de transacciones implícitas en SQL Server 2012 y agregaré un registro nuevo a la tabla HumanResources.Department de la base de datos AdventureWorks2012.
Ejecuto los siguientes comandos para agregar un registro.

SET IMPLICIT TRANSACTIONS ON
go
Insert into HumanResources.Deparment(Name,GroupName,ModifiedDate)
values('Software Developer','Research and Development',getdate())
go

Consulto la tabla para ver que se agregó el registro a la tabla.

SELECT * FROM HumanResources.Department ORDER BY DepartmentID DESC

Hasta este punto, si ejecuto ROLLBACK puedo deshacer la modificación a la tabla, si vuelvo a consultar la tabla los datos son los mismos que estaban almacenados antes del INSERT.

ROLLBACK

Vuelvo a ejecutar el INSERT si ahora deseo que la modificación sea permanente, tengo que ejecutar un COMMIT.

Vuelvo a consultar los datos de la tabla y comprobamos que el registro se guardo de forma permanente.

En este modo, si hay comandos que no han sido confirmados (COMMIT) o rechazados (ROLLBACK), SQL Management nos avisa al momento de cerrar la aplicación.