Microsoft® SQL Server™ 2005: Applied Techniques Step by Step

Defining Transactions in SQL Server

Now we will have a look at how transactions can be defined and handled in SQL Server 2005. SQL Server provides different ways to handle transactions, which can be defined on a per-connection basis. Every connection can use the mode it needs to accomplish its requirements. The different modes are:

• Auto-commit transactions

• Explicit transactions

• Implicit transactions

Auto-Commit Mode

SQL Server handles everything as a transaction. It never modifies data outside of a transaction. Therefore, SQL Server has to define a transaction on its own when a transaction is not defined by the developer. Transactions defined by SQL Server are called auto-commit transactions. Auto-commit mode is the default mode in SQL Server. ...