Autocommit defaults to be on for SQL 2008. As SQL 2008 BOL says (see
http://msdn.microsoft.com/en-us/library/ms187878%28v=SQL.100%29.aspx)
"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.
Tom
