Why isn't SET XACT_ABORT ON the default behavi

问题:

Reading things like this post on Dan Guzman's blog, I wonder: why isn't SET XACT_ABORT ON the default behavior? Is there a case where it's harmful, or much less desirable/efficient than SET XACT_ABORT OFF?

回答1:

It's an automatic response to an error, it's more desirable if you can handle the error and recover from it. If the transaction automatically rolls back then you don't get this opportunity.

The problem Dan mentions in his blog arises because of the abort from the client, within SQL this abort doesn't exist. Hence within SQL the default is not to automatically abort transactions.