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:
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. ...