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?
Asked
Active
Viewed 5,133 times
24
Stephen Turner
- 7,125
- 4
- 51
- 68
Daniel
- 47,404
- 11
- 101
- 179
-
Funny, I just came across similar info and had the same question - "what am I missing, why would you want it to be this way by default?" – Mark Sowul Oct 31 '11 at 17:43
-
2[Related](http://stackoverflow.com/q/1150032/73226) – Martin Smith Oct 31 '11 at 21:45
-
1Related...but a very different question. – Daniel Nov 21 '11 at 21:49
-
See http://stackoverflow.com/search?tab=votes&q=%22set%20xact_abort%22%20user%3a27535 please – gbn Jul 07 '12 at 16:56
-
agreed. there should be an option in the SSMS Advanced Execution Query Options window (along with 'SET NOCOUNT", "SET ARITHABORT", etc...) and that option should get copied to new SPs (in the same way that "ANSI_NULLS" and "QUOTED_IDENTIFIER" are) – Spongman Apr 01 '16 at 17:21
1 Answers
16
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.
Stephen Turner
- 7,125
- 4
- 51
- 68
-
Also see [this related question](http://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on). – Fernando Correia Jul 07 '12 at 16:06