SQL Server: IMPLICT_TRANSACTIONS Option

The IMPLICIT_TRANSACTIONS option controls how transactions are handled. Transactions can be specified explicitly, implicitly, or automatically. In autocommit mode, which is the default, each Transact-SQL statement is treated as a separate transaction, and each statement is automatically committed when it successfully executes. In this scenario if DBA/Developer set the IMPLICIT_TRANSACTIONS option to ON, transactions will be implicitly created. This means that transactions will be automatically started if there is no current transaction, and must be explicitly committed or rolled back. Any uncommitted implicit transactions are rolled back when the user disconnects. DBA/Developer should not add the SET IMPLICIT_TRANSACTIONS OFF statement. This is the default setting, and all Transact- SQL would use autocommit mode. Each DML statement would be considered a separate transaction and automatically committed if it executed successfully. DBA/Developer should not set the transaction isolation level to READ COMMITTED. The transaction isolation level controls how a transaction behaves when there are other concurrent transactions. However, in this scenario, transactions are not used. Therefore, this would not be applicable. DBA/Developer should not include a custom error-handler for each DML statement because this would require more effort than necessary. DBA/Developer can check the value returned by the @@ERROR function for each DML statement and include blocks of code to perform the desired actions. However, in this scenario, DBA/Developer wants to have minimal development effort.