How do you set autocommit in an SQL Server session?

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 ONUPDATE MyTable SET MyField = 1 WHERE MyId = 1COMMIT TRANSACTION

This will not write a change to the database:

SET IMPLICIT_TRANSACTIONS ONUPDATE MyTable SET MyField = 1 WHERE MyId = 1ROLLBACK TRANSACTION

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

SET IMPLICIT_TRANSACTIONS OFFUPDATE MyTable SET MyField = 1 WHERE MyId = 1ROLLBACK TRANSACTION

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

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.

Autocommit is SQL Server's default transaction management mode. (SQL 2000 onwards)

Ref: Autocommit Transactions