A while ago I blogged on a gotcha to watch out for when using System.Transactions with SQL Server 2005. Basically, if you open a second connection to SQL Server from inside a using block in which you create a new TransactionScope, SQL Server will automatically promote the transaction to a distributed one in which the Distributed Transaction Coordinator (DTC) manages the transaction with the two-phase commit protocol. While this is a more expensive operation, it\’s exactly what you want if you want to update two databases within the same transaction, such that both will either succeed or get rolled back. However, SQL Server 2005 would promote the transaction to distributed even though both connections are to the same database, using the same connection string! Most of the time, you would wish instead to use a more efficient native transaction, but to do so, you would have to open a single database connection. SQL Server 2008, however, addresses this problem by not promoting the transaction to distributed when multiple connections are open to the same database with the same connection string (i.e., the connections are pooled).\r\n

To verify this, all you have to do is add a watch in the Visual Studio debugger for Transaction.Current, then expand it to the TransactionInformation property, looking at DistributedIdentifier. If it shows up as an empty Guid, the transaction is local; otherwise, it has been promoted to distributed.