How do you set autocommit in an SQL Server session?

Question

How do you set autocommit in an SQL Server session?

Answer 1

You can turn autocommit ON by setting implicit_transactions OFF:

SET IMPLICIT_TRANSACTIONS OFF 

When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually.

Maybe an example is clearer. This will write a change to the database:

SET IMPLICIT_TRANSACTIONS ON UPDATE MyTable SET MyField = 1 WHERE MyId = 1 COMMIT TRANSACTION 

This will not write a change to the database:

SET IMPLICIT_TRANSACTIONS ON UPDATE MyTable SET MyField = 1 WHERE MyId = 1 ROLLBACK TRANSACTION 

The following example will update a row, and then complain that there's no transaction to commit:

SET IMPLICIT_TRANSACTIONS OFF UPDATE MyTable SET MyField = 1 WHERE MyId = 1 ROLLBACK TRANSACTION 

Like Mitch Wheat said, autocommit is the default for Sql Server 2000 and up.

Answer 2

I wanted a more permanent and quicker way. Because I tend to forget to add extra lines before writing my actual Update/Insert queries.

I did it by checking SET IMPLICIT_TRANSACTIONS check-box from Options. To navigate to Options Select Tools>Options>Query Execution>SQL Server>ANSI in your Microsoft SQL Server Management Studio.

Just make sure to execute commit or rollback after you are done executing your queries. Otherwise, the table you would have run the query will be locked for others.