sql server - How do you disable autocommit in azure data studio

Question

We use Azure Data Studio for our SQL Server queries. However, it automatically commits everything and I cannot find an option to disable this. I found this page https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/setting-the-commit-mode?view=sql-server-ver15 but I cannot find any place in the connection settings where I can set the SQL_ATTR_AUTOCOMMIT attribute

Answer 1

Autocommit is the default behavior in SQL Server, to turn it off set implicit_transactions on:

Sets the BEGIN TRANSACTION mode to implicit, for the connection.

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
• OPEN
• UPDATE
• DROP

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.

So just open a query window (session) and run:

set implicit_transactions on

In Azure Data Studio you can set this by default here:

But if you do, and you're writing a reusable script, remember to add the setting to the script itself so another user doen't run it with different transaction behavior.