SQL with Manoj

–> Question:

I remember for the previous version, “autocommit” is defaulted to be on. How about SQL Server 2008 Management Studio? Where can we change its default?
 

–> My Answer:

Go to SSMS, Menu, Tools, Options, Query Execution, SQL Server, ANSI.

Here check the option on SET IMPLICIT_TRANSACTIONS
 

–> Answer by Tom Cooper:

Autocommit defaults to be on for SQL Server 2008. As per MSBoL it says:

“Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode.”

There is a user option named IMPLICIT_TRANSACTIONS which will set implicit transactions ON when the connection is made by a user that has that option. But it only works if you are using DBLIB which is an old deprecated connection method that has many restrictions and should only be used for legacy applications. That user option is ignored if you are using ODBC or OLEDB connections.
 

–> Answer by Steven Wang:

If you SET IMPLICIT_TRANSACTIONS on then you need manually to rollback and commit.

by default the IMPLICIT_TRANSACTIONS is set to off and commit is automatically done.
 

Ref link.