Is Every SQL Statement a Transaction

 Yes. In SQL Server, every SQL statement executes within a transaction. If we do not explicitly start a transaction using BEGIN TRANSACTION, SQL Server automatically creates an auto commit transaction for the statement. The statement starts a transaction, executes, and commits automatically if successful.

Therefore, every SQL statement is transactional, but not every transaction is an explicit user-defined transaction. Every SQL statement in SQL Server executes within a transaction. In the default auto commit mode, SQL Server automatically creates a transaction, executes the statement, and commits it when successful. This behavior ensures ACID compliance, guarantees atomicity and durability, and allows SQL Server to recover consistently after failures. Explicit transactions simply allow developers to group multiple statements into a single logical unit of work; they do not introduce transactions where none previously existed.

SQL Server supports three transaction modes:

Ø  Auto commit Transactions (Default)

Ø  Explicit Transactions

Ø  Implicit Transactions

Auto commit Transaction (Default Mode)

This is what most applications use.

Example:

UPDATE Employee

SET Salary = Salary + 1000

WHERE EmployeeID = 1;

Here BEGIN TRAN COMMIT is not visible.

However internally SQL Server performs:

BEGIN TRANSACTION àExecute Statementà COMMIT TRANSACTION automatically. This is called Auto commit Mode.

Internal Architecture when auto commit is running