Autocommit mode is the default transaction management mode of Microsoft® SQL Server™. 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 SQL Server connection 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 DB-Library, ODBC, OLE DB, and ADO.
A SQL Server connection 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, SQL Server returns to autocommit mode.
In autocommit mode, it sometimes appears as if SQL Server has rolled back an entire batch instead of just one SQL statement. This happens only if the error encountered is a compile error, not a run-time error. A compile error prevents SQL Server from building an execution plan, so nothing in the batch is executed. Although it appears that all the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. In this example, none of the INSERT statements in the third batch are executed because of a compile error. It appears that the first two INSERT statements are rolled back when they are never executed.
USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUSE (3, 'ccc') /* Syntax error */
GO
SELECT * FROM TestBatch /* Returns no rows */
GO
In this example, the third INSERT statement generates a run-time duplicate primary key error. The first two INSERT statements are successful and committed, so they remain after the run-time error.
USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUES (1, 'ccc') /* Duplicate key error */
GO
SELECT * FROM TestBatch /* Returns rows 1 and 2 */
GO
SQL Server version 7.0 introduces delayed name resolution, in which object names are not resolved until execution time. Earlier versions of SQL Server resolved names at compile time. Some errors that were compile-time errors and prevented the execution of a batch in earlier versions of SQL Server are now run-time errors in SQL Server 7.0. In this example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.
USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBch VALUES (3, 'ccc') /* Table name error */
GO
SELECT * FROM TestBatch /* Returns rows 1 and 2 */
GO