--- description: "SET IMPLICIT_TRANSACTIONS (Transact-SQL)" title: "SET IMPLICIT_TRANSACTIONS (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/16/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.reviewer: "" ms.technology: t-sql ms.topic: "language-reference" f1_keywords: - "IMPLICIT_TRANSACTIONS" - "SET IMPLICIT_TRANSACTIONS" - "IMPLICIT_TRANSACTIONS_TSQL" - "SET_IMPLICIT_TRANSACTIONS_TSQL" dev_langs: - "TSQL" helpviewer_keywords: - "implicit transactions" - "transactions [SQL Server], implicit" - "connections [SQL Server], implicit transaction mode" - "SET IMPLICIT_TRANSACTIONS statement" - "IMPLICIT_TRANSACTIONS option" ms.assetid: a300ac43-e4c0-4329-8b79-a1a05e63370a author: markingmyname ms.author: maghan monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # SET IMPLICIT_TRANSACTIONS (Transact-SQL) [!INCLUDE [sql-asdb-asdbmi-asa-pdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)] Sets the BEGIN TRANSACTION mode to *implicit*, for the connection. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ```syntaxsql SET IMPLICIT_TRANSACTIONS { ON | OFF } ``` [!INCLUDE[sql-server-tsql-previous-offline-documentation](../../includes/sql-server-tsql-previous-offline-documentation.md)] ## 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: :::row::: :::column::: ALTER TABLE :::column-end::: :::column::: FETCH :::column-end::: :::column::: REVOKE :::column-end::: :::row-end::: :::row::: :::column::: BEGIN TRANSACTION :::column-end::: :::column::: GRANT :::column-end::: :::column::: SELECT (See exception below.) :::column-end::: :::row-end::: :::row::: :::column::: CREATE :::column-end::: :::column::: INSERT :::column-end::: :::column::: TRUNCATE TABLE :::column-end::: :::row-end::: :::row::: :::column::: DELETE :::column-end::: :::column::: OPEN :::column-end::: :::column::: UPDATE :::column-end::: :::row-end::: :::row::: :::column::: DROP :::column-end::: :::column::: :::column-end::: :::column::: :::column-end::: :::row-end:::   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();` or `SELECT 1, 'ABC';` do not require transactions. - Implicit transactions may unexpectedly be ON due to ANSI defaults. For details see [SET ANSI_DEFAULTS (Transact-SQL)](../../t-sql/statements/set-ansi-defaults-transact-sql.md). 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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Native Client OLE DB Provider for [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], and the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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. ```sql 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. ```sql -- Transact-SQL. go -- 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. ```sql -- 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 INSERTs, == 1 [D.05] @@TranCount, after INSERTs, == 0 ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] ## See Also [ALTER TABLE (Transact-SQL)](../../t-sql/statements/alter-table-transact-sql.md) [BEGIN TRANSACTION (Transact-SQL)](../../t-sql/language-elements/begin-transaction-transact-sql.md) [CREATE TABLE (Transact-SQL)](../../t-sql/statements/create-table-transact-sql.md) [DELETE (Transact-SQL)](../../t-sql/statements/delete-transact-sql.md) [DROP TABLE (Transact-SQL)](../../t-sql/statements/drop-table-transact-sql.md) [FETCH (Transact-SQL)](../../t-sql/language-elements/fetch-transact-sql.md) [GRANT (Transact-SQL)](../../t-sql/statements/grant-transact-sql.md) [INSERT (Transact-SQL)](../../t-sql/statements/insert-transact-sql.md) [OPEN (Transact-SQL)](../../t-sql/language-elements/open-transact-sql.md) [REVOKE (Transact-SQL)](../../t-sql/statements/revoke-transact-sql.md) [SELECT (Transact-SQL)](../../t-sql/queries/select-transact-sql.md) [SET Statements (Transact-SQL)](../../t-sql/statements/set-statements-transact-sql.md) [SET ANSI_DEFAULTS (Transact-SQL)](../../t-sql/statements/set-ansi-defaults-transact-sql.md) [@@TRANCOUNT (Transact-SQL)](../../t-sql/functions/trancount-transact-sql.md) [TRUNCATE TABLE (Transact-SQL)](../../t-sql/statements/truncate-table-transact-sql.md) [UPDATE (Transact-SQL)](../../t-sql/queries/update-transact-sql.md)