Types of Transactions in SQL Server

转自 http://www.sqlservergeeks.com/blogs/sarab/sql-server-bi/184/types-of-transactions-in-sql-server

There are four types of transactions in SQL Server.

Automatic
Each command in SQL Server is considered to be a transaction bydefault. Example could be any DML operation, Insert, Update, Delete. 

Implicit
These are ANSI SQL-92 compliant automatic transactions. They are initiated when any sort of DDL, DML command is executed. User has to explicitly commit the transaction. To use this you  need to set IMPLICIT_Transaction command. By Default OLE DB and ODBC connections enable ANSI_Defaults switch which enables this as well.


User Defined
A User defined transaction is when the user starts a Transaction with Begin Tran and complete it with Commit Tran\RollBack.

Distributed
A Transaction that serves multiple servers are known as distributed Transaction. In simple words, a transaction that needs to work on multiple servers. SQL Server uses Microsoft Distributed Transaction Coordinator also known as MSDTC to make it possible. If you want to initiate a Distributed transaction you can do so with the help of following Command: Begin Distributed Transaction.