SET IMPLICIT_TRANSACTIONS ON

 

SET IMPLICIT_TRANSACTIONS ON sets the transactions to use COMMIT before a transaction is committed. Default setting is OFF and when it is set to OFF transactions will be committed automatically.

When the setting is set to on, following statements will start a  transaction implicitly.

 

ALTER TABLE	CREATE	DELETE	DROP
FETCH	GRANT	INSERT	OPEN
REVOKE	SELECT	TRUNCATE	UPDATE
MERGE	ALTER SERVER

Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.

SET IMPLICIT_TRANSACTIONS ON

INSERT INTO Emp
(Name,AddressI,City)
VALUES
('Name','Add','C1')

SELECT @@TRANCOUNT 

Output of this is 1 which mean there is one transaction is open which was created by setting IMPLICIT_TRANSACTIONS to ON.

Above transaction has to be finished with COMMIT or ROLLBACK.

You can monitor the trancount from perfmon as well.

 

User Option Setting

You have a setting for implicit transaction in the server properties in connection page as shown below.

This setting is for dblib network library connections and this setting has no effect on ODBC or OLEDB connections.

Also, you can set the above setting by running following script.

EXEC sys.sp_configure N'user options', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

You are not able to set this if you have contained databases in the Server.

Msg 12830, Level 16, State 1, Procedure sp_configure, Line 166
The sp_configure 'user options' setting must be zero if the Database Engine has contained databases.

However, you can set SET IMPLICIT_TRANSACTIONS ON inside a contained databases without any issues.