What is ARITHABORT setting

Question

Please share the knowledge what is ARITHABORT setting?

how to turn on ARITHABORT setting and how to turn off ARITHABORT ?

can i turn ARITHABORT setting at the first line of store procedure ?

please guide me. thanks

Answer 1

You can turn ARITHABORT on/off with SET ARITHABORT ON; and SET ARITHABORT OFF;.

However, there is seldom a need to explicitly change the ARITHABORT session setting. ARITHABORT is implicitly ON when connecting to a database in SQL 2005 or later compatibility level when ANSI_WARNINGS is also ON. Modern SQL Server client APIs connect with ANSI_WARNINGS ON so ARITHABORT is implicitly ON and will remain so unless explicitly overridden with SET ARITHABORT OFF;. This is called out the the documentation:

When ANSI_WARNINGS has a value of ON and the database compatibility level is set to 90 or higher then ARITHABORT is implicitly ON regardless of it's value setting. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must be explicitly set to ON.

The ARITHABORT ON setting (implicit or explicit) is required to use features like filtered indexes, indexed views. etc. One had to explicitly turn on ARITHABORT back in the SQL 2000 days (or set it as the default setting via sp_configure 'user options'). Now that it's controlled by the database compatibility level, the implication is it will be on as long as one is using a supported SQL Server version (SQL 2012 or later, including Azure SQL Database) and an API that sets ANSI_WARNINGS ON.

Erland added that SSMS explicitly sets ARITHABORT ON even though it's already implicitly ON with a SQL 2005+ compatibility level database. Although the ARITHABORT behavior will be the same regardless of whether it's set explicitly or implicitly, the gotcha is the setting is also part of the cache key. This can result in multiple cached plans (which may be different) for the same proc or query due to the implicit versus explict setting.

Answer 2

It is not often that I need to correct Dan, but this is such a case. ARITHABORT if OFF by default when you connect from a client program. (But to confuse, SSMS always turns it on.)

Under normal circumstances there is absolutely no difference between ARITHABORT ON and OFF. Dan suggests that it has to be ON for filtered indexes and indexed views, but that is only true if you are in compatibility level 80, that is, SQL 2000, which is not even supported on SQL 2012 and on.

You may ask what the abnormal circumstances are when ARITHABORT does have an effect. That is when the setting ANSI_WARNINGS is OFF. If both are OFF, an arithmetic error like like division by zero and overflow will not yield an error but only a warning. But you should never have ANSI_WARNINGS OFF. (And that setting must be ON for filtered indexes and indexed views to work.)