If you have created a complex transaction that includes several parts, one part of which has a higher probability of rolling back the transaction than the others, better performance will be provided if you locate the most likely to fail part of the transaction at the front of the greater transaction. This way, if this more-likely-to-fail transaction has to roll back because of a failure, there has been no resources wasted on the other less-likely-to-fail transactions. [6.5, 7.0, 2000, 2005] Added 7-2-2003
*****
Generally, you only want to encapsulate your Transact-SQL code inside a transaction if it is going to modify the database. Transactions help to ensure database consistency and are an important tool in the developer’s toolbox.
But if you put non-database modifying Transact-SQL in a transaction, you are producing unnecessary overhead for your application and SQL Server. For example, Transact-SQL used for creating reports doesn’t usually need the benefits (and the extra overhead) associated with transactions. [6.5, 7.0, 2000, 2005] Updated 10-30-2003
]]>