SET IMPLICIT_TRANSACTIONS (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Warehouse in Microsoft Fabric
Sets the BEGIN TRANSACTION mode to implicit, for the connection.
Transact-SQL syntax conventions
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, the system is in implicit transaction mode. This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first:
ALTER TABLE
FETCH
REVOKE
BEGIN TRANSACTION
GRANT
SELECT (See exception below.)
CREATE
INSERT
TRUNCATE TABLE
DELETE
MERGE
UPDATE
DROP
OPEN
When OFF, each of the preceding T-SQL statements is bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement. When OFF, we say the transaction mode is autocommit. If your T-SQL code visibly issues a BEGIN TRANSACTION, we say the transaction mode is explicit.
There are several clarifying points to understand:
When the transaction mode is implicit, no unseen BEGIN TRANSACTION is issued if @@trancount > 0 already. However, any explicit BEGIN TRANSACTION statements still increment @@TRANCOUNT.
When your INSERT statements and anything else in your unit of work is finished, you must issue COMMIT TRANSACTION statements until @@TRANCOUNT is decremented back down to 0. Or you can issue one ROLLBACK TRANSACTION.
SELECT statements that do not select from a table do not start implicit transactions. For example
SELECT GETDATE();orSELECT 1, 'ABC';do not require transactions.Implicit transactions may unexpectedly be ON due to ANSI defaults. For details see SET ANSI_DEFAULTS (Transact-SQL).
IMPLICIT_TRANSACTIONS ON is not popular. In most cases where IMPLICIT_TRANSACTIONS is ON, it is because the choice of SET ANSI_DEFAULTS ON has been made.
The SQL Server Native Client OLE DB Provider for SQL Server, and the SQL Server Native Client ODBC driver, automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. SET IMPLICIT_TRANSACTIONS defaults to OFF for connections with the SQLClient managed provider, and for SOAP requests received through HTTP endpoints.
To view the current setting for IMPLICIT_TRANSACTIONS, run the following query.
DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3) = 'OFF';
IF ( (2 & @@OPTIONS) = 2 ) SET @IMPLICIT_TRANSACTIONS = 'ON';
SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS;
Examples
The following Transact-SQL script runs a few different test cases. The text output is also provided, which shows the detailed behavior and results from each test case.
-- Transact-SQL.
-- Preparations.
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
GO
WHILE (@@TranCount > 0) COMMIT TRANSACTION;
GO
IF (OBJECT_ID(N'dbo.t1',N'U') IS NOT NULL) DROP TABLE dbo.t1;
GO
CREATE table dbo.t1 (a INT);
GO
PRINT N'-------- [Test A] ---- OFF ----';
PRINT N'[A.01] Now, SET IMPLICIT_TRANSACTIONS OFF.';
PRINT N'[A.02] @@TranCount, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS OFF;
GO
INSERT INTO dbo.t1 VALUES (11);
INSERT INTO dbo.t1 VALUES (12);
PRINT N'[A.03] @@TranCount, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N' ';
PRINT N'-------- [Test B] ---- ON ----';
PRINT N'[B.01] Now, SET IMPLICIT_TRANSACTIONS ON.';
PRINT N'[B.02] @@TranCount, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS ON;
GO
INSERT INTO dbo.t1 VALUES (21);
INSERT INTO dbo.t1 VALUES (22);
PRINT N'[B.03] @@TranCount, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
COMMIT TRANSACTION;
PRINT N'[B.04] @@TranCount, after COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N' ';
PRINT N'-------- [Test C] ---- ON, then BEGIN TRAN ----';
PRINT N'[C.01] Now, SET IMPLICIT_TRANSACTIONS ON.';
PRINT N'[C.02] @@TranCount, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS ON;
GO
BEGIN TRANSACTION;
INSERT INTO dbo.t1 VALUES (31);
INSERT INTO dbo.t1 VALUES (32);
PRINT N'[C.03] @@TranCount, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
COMMIT TRANSACTION;
PRINT N'[C.04] @@TranCount, after a COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;
PRINT N'[C.05] @@TranCount, after another COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N' ';
PRINT N'-------- [Test D] ---- ON, INSERT, BEGIN TRAN, INSERT ----';
PRINT N'[D.01] Now, SET IMPLICIT_TRANSACTIONS ON.';
PRINT N'[D.02] @@TranCount, at start, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
SET IMPLICIT_TRANSACTIONS ON;
GO
INSERT INTO dbo.t1 VALUES (41);
BEGIN TRANSACTION;
INSERT INTO dbo.t1 VALUES (42);
PRINT N'[D.03] @@TranCount, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
COMMIT TRANSACTION;
PRINT N'[D.04] @@TranCount, after a COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;
PRINT N'[D.05] @@TranCount, after another COMMIT, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
-- Clean up.
SET IMPLICIT_TRANSACTIONS OFF;
GO
WHILE (@@TranCount > 0) COMMIT TRANSACTION;
GO
DROP TABLE dbo.t1;
GO
Next is the text output from the preceding Transact-SQL script.
-- Text output from Transact-SQL:
-------- [Test A] ---- OFF ----
[A.01] Now, SET IMPLICIT_TRANSACTIONS OFF.
[A.02] @@TranCount, at start, == 0
[A.03] @@TranCount, after INSERTs, == 0
-------- [Test B] ---- ON ----
[B.01] Now, SET IMPLICIT_TRANSACTIONS ON.
[B.02] @@TranCount, at start, == 0
[B.03] @@TranCount, after INSERTs, == 1
[B.04] @@TranCount, after COMMIT, == 0
-------- [Test C] ---- ON, then BEGIN TRAN ----
[C.01] Now, SET IMPLICIT_TRANSACTIONS ON.
[C.02] @@TranCount, at start, == 0
[C.03] @@TranCount, after INSERTs, == 2
[C.04] @@TranCount, after a COMMIT, == 1
[C.05] @@TranCount, after another COMMIT, == 0
-------- [Test D] ---- ON, INSERT, BEGIN TRAN, INSERT ----
[D.01] Now, SET IMPLICIT_TRANSACTIONS ON.
[D.02] @@TranCount, at start, == 0
[D.03] @@TranCount, after INSERTs, == 2
[D.04] @@TranCount, after a COMMIT, == 1
[D.05] @@TranCount, after another COMMIT, == 0
Here's the result set.
See Also
ALTER TABLE (Transact-SQL)
BEGIN TRANSACTION (Transact-SQL)
CREATE TABLE (Transact-SQL)
DELETE (Transact-SQL)
DROP TABLE (Transact-SQL)
FETCH (Transact-SQL)
GRANT (Transact-SQL)
INSERT (Transact-SQL)
MERGE (Transact-SQL)
OPEN (Transact-SQL)
REVOKE (Transact-SQL)
SELECT (Transact-SQL)
SET Statements (Transact-SQL)
SET ANSI_DEFAULTS (Transact-SQL)
@@TRANCOUNT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)