Hi Ken,

Here's my take on your question.

When you choose to use a SQL transaction, you are defining the boundary of
the transaction in your stored procedure or application code.  This will
work fine for many self contained applications that are not intended to be
aggregated into larger transactions in a compose-able manner.

When you use COM+ transaction attributes, you are describing how your
component can participate in a transaction.  For instance, you can say that
your component will not participate in a transaction, that your component
always opens a new transaction context, or even that your component can
participate in an existing transaction - starting a new one only if one is
not already started.

This kind of declarative and compose-able transaction ability lets you
program components that can be placed into transactions in ways that the
original programmer didn't know of at the time a component was coded.

Contrast this to the self contained, and non-compose-able SQL transaction
object, and you end up with several useful transaction approaches to use as
you need them in your application designs.

As for efficiency, that is a hard to pin-down term without more specifics.  
There is programmer efficiency, network efficiency, throughput factors,
etc.  That said, it is fair to say that some people feel that COM+
transactions and the use of the distributed transaction coordinator
introduces added scale-out issues to consider before making a statement
that places one transaction approach over the other with an "always"
attached.  In the SQL transaction, the one SQL server is the arbibitrator
of the transaction bounds, so there is less communication going on over the
network.  However, in the case where you need to coordinate
two-phase-commit over several resources that are transaction enabled, you
end up using a coordinator anyhow, so the only gain is in the
single-database, database-only, multi-table consistent update case.

I hope this helps,

Dan Rogers
Microsoft Corporation
--------------------

microsoft.public.dotnet.framework.aspnet.webservices:25891