Efficiency of transaction in code vs. DB

Question

What is more efficient -- having a IDbTransaction in the .net code or handling it in the database? Why?

What are the possible scenarios in which either should be used?

Answer 1

When it comes to connection-based transactions (IDbTransaction), the overall performance should be pretty similar - but by handling it in the .NET code you make it possible to conveniently span multiple database operations on the same connection. If you are doing transaction management inside TSQL you should really limit it to the single TSQL query. There may well be an extra round-trip for the begin/end, but that isn't likely to hurt you.

It is pretty rare (these days) that I'd manually write TSQL-based transactions - maybe if I was writing something called directly by the server via an agent (rather than from my own application code).

The bigger difference is between IDbTransaction and TransactionScope see Transactions in .net for more, but the short version is that TransactionScope is slightly slower (depending on the scenario), but can span multiple connections / databases (or other resources).