Well, it depends! (Like you didn’t see that coming?)
Here is what they have in common: both statements are issued by a SET command. That’s it, really, nothing more. But that link gives you some idea about the differences for the two statements, as it has the ARITHABORT listed as a ‘Query Execution Statement’, and the XACT_ABORT listed as a ‘Transaction Statement’.
I’ve provided links to the MSDN entries for both statements, as there is a wealth of information there that I don’t want (or need) to repeat again here. So, go have a look and then come back, I’ll wait.
OK, so now you know the difference, right? ARITHABORT, by itself, will terminate a query that has either an overflow or divide-by-zero error. The question now becomes “what does that query termination do to my transaction?” That’s a great question, and I will point you back to the MSDN entry that explains how ANSI_WARNINGS plays a role as well as your database compatibility level. There’s info in there explaining how you could either have the query, the batch, or a transaction be rolled back. Bottom line here: know what SET options have been defined for your connection (HINT: ARITHABORT is OFF by default).
XACT_ABORT is all about the transaction. If any statement in a transaction causes a run-time error the entire transaction will be rolled back. The MSDN entry has a great example showing how you can still insert some data by setting XACT_ABORT to OFF (see the example at the bottom).
How would you use the two SET options together? I’m not sure you would, but I suppose in a mad scientist kind of way you might want to use ARITHABORT to ensure that only NULL values are inserted and also use XACT_ABORT to ensure the transaction is not rolled back. I’m not one to rely on a bunch of non-default options in order to make my code work, I tend to keep things as simple as possible in order to reduce the amount of administrative overhead.
They are two different options, each with a very specific function to perform.
thomas thanks for the posting this summary. it just so happens that @spaghettidba wrote post containing a stored procedure template which uses both ARITHABORT and XACT_ABORT.
XACT_ABORT was ON to ensure transaction rollback
ARITHABORT was ON to ensure compatibility with indexed view and computed columns
http://msdn.microsoft.com/en-us/library/ms175088.aspx
the BOL link to default settings could be somewhat misleading as there are many different defaults depending on the tool used and library used
Robert,
Thanks for the info, and for the links. I had not thought about indexed views and computed columns. I agree that the BOL can be confusing at times, they seemed to pack a lot of little details into the entries for these two options.
Just based on their names, their usage scenarios make sense. Thanks for the summary!