ARITHABORT and XACT_ABORT: Which One Do I Want to Use?

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.

 

 

4 thoughts on “ARITHABORT and XACT_ABORT: Which One Do I Want to Use?”

  1. 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

    Reply
    • 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.

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.