Set Implicit Transaction ON on Management Studio for Azure SQL Database

Question

In SQL Server Management Studio I check the option Tools/Options/Query Execution/SQL Server/ANSI/SET IMPLICIT TRANSACTIONS

This works for SQL Server database but It seems not works if I connect to Azure SQL Database

How can set IMPLICIT_TRANSACTIONS to ON by default for Azure SQL Database?

Edit

I tried this:

from Object Explorer I select the database, right click, new Query and execute this script:

CREATE TABLE T1 (a INT)
ROLLBACK
SELECT * FROM T1 

The result is as expected: Invalid object name 'T1'

Now i click on "Change connection" button

I reconnect to the same Azure SQL Server instance

I select the same Database from the database combo list and execute the same script.

Now the error is "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

Answer 1

I cannot reproduce the issue. First I tested that SQL Server and SQL Azure behave both the same for implicit transactions with the following script.

-- 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 INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));  

COMMIT TRANSACTION;  

PRINT N'[D.05] @@TranCount, after INSERTs, == ' + CAST(@@TRANCOUNT AS NVARCHAR(10));  

go  



-- Clean up.  

SET IMPLICIT_TRANSACTIONS OFF;  

go  

WHILE (@@TranCount > 0) COMMIT TRANSACTION;  

go  

DROP TABLE dbo.t1;  

go

The following results were the same on both SQL Server and Azure SQL Database (DTU model).

-------- [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

Then I configured implicit transactions as default on SSMS, commented the SET IMPLICIT_TRANSACTIONS statements, and on Tests B, C and D, I received the same results.

I tested this with SSMS 17.8.1. Which version of SSMS you are using? Please download the latest version from here.

Answer 2

I just followed the document: SET IMPLICIT_TRANSACTIONS (Transact-SQL).

SET IMPLICIT_TRANSACTIONS { ON | OFF }

My SQL server version is 12.0, I tested in SSMS 17.9 and 18.1, run the SQL in Mydatabase:

Update:

Set IMPLICIT_TRANSACTIONS:

But from the document Set IMPLICIT_TRANSACTIONS, it says:

• 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.

I open the new query and SET ANSI_DEFAULTS ON, it still worked:

Hope this helps