MS SQL Server 2012 - DBA Articles

AUTOCOMMIT Transaction Process Flow

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 complets. 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.
Compile and Run-time Errors
In autocommit mode, it sometimes appears as if an instance of the Database Engine has rolled back an entire batch instead of just one SQL statement. This happens if the error encountered is a compile error, not a run-time error.
Compile Error Example

-- Example 66 --

USE EBusiness

CREATE TABLE Product_Test

(

Prod_Code varchar(5) PRIMARY KEY,

Prod_Name varchar(50) NOT NULL,

)

INSERT INTO Product_Test VALUES ('P01','Product A');

INSERT INTO Product_Test VALUES ('P02','Product B');

INSERT INTO Product_Test VALUES ('P03','Product C); -- Syntax Error

SELECT * FROM Product_Test � Return No Record(s)

Query Output Screen

Run-time Error Example

-- Example 67 --

USE EBusiness

CREATE TABLE Product_Test

(

Prod_Code varchar(5) PRIMARY KEY,

Prod_Name varchar(50) NOT NULL,

)

INSERT INTO Product_Test VALUES ('P01','Product A');

INSERT INTO Product_Test VALUES ('P02','Product B');

INSERT INTO Product_Test VALUES ('P01','Product C'); -- Duplicate Record

SELECT * FROM Product_Test -- Return P01 and P02 Records.

Query Output Screen

* * * * *

Your Comment To AUTHOR