Articles → SQL SERVER → Autocommit Transactions In SQL Server

Autocommit Transactions In SQL Server

In this article, we will discuss auto-commit transactions.

What Are Auto-Commit Transactions?

Auto-commit transactions are the SQL Server’s default mode of committing each t-SQL statement if the statement executes successfully. Otherwise, the transaction is rolled back.

Let us work on some cases to get more clarity on auto-commit transactions.

Enable Auto-Commit Mode

Auto-commit is the default mode of the SQL server, but still, you can enable it using the following command: -

SET IMPLICIT_TRANSACTIONS OFF

Prerequisite

Let us first create a table with the following schema.

Picture showing the schema of the database table

Click to Enlarge

Once the table is created, we can talk about different scenarios.

Case 1: When T-SQL Statement Contains Compile Time Error

Consider the following T-SQL statements: -

Set identity_insert TestTable On 

Insert into [dbo].[TestTable] values(1,'Test1')
Insert into [dbo].[TestTable] values(1,'Test2')

Set identity_insert TestTable Off

If you run the above commands, you will get the compile time error and everything will be rolled back.

The reason for this is that if a syntax error occurs, no execution plan is created by the database engine. As a result, nothing is executed.

Case 2: Invalid Object Name (Or Other Run Time Error)

Consider following SQL statements

Set identity_insert TestTable On 

Insert into [dbo].[TestTable] (Id, Name) values(1,'Test1')
Insert into [dbo].[TestTable1] values(2,'Test2')

Set identity_insert TestTable Off

In the above code, the object "TestTable1" does not exists in the database. But when you run the above batch, one value "Test1" gets inserted into the table.

The reason is database engine resolves the object name during execution. It means that while executing the query, database engine searches the object in the repository. If the object is not found then run time error will be thrown by database engine. This is deferred name resolution.

Picture showing the error when the query is trying to be executed on non-existent table

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Tuesday, October 10, 2017

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**