please guide me. thanks
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.
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.)
Hi, Erland. I wasn't as clear as I should have been in my answer as I didn't call out ANSI_WARNINGS and the differences between the implicit versus explict ARITHAVORT setting. I revised my answer to make this clearer. But my original answer was not wrong because ARITHABORT is implicitly ON rather than OFF when one connects with a modern clients that set ANSI_WARNINGS ON.
ARITHABORT is implicitly ON rather than OFF when one connects with a modern clients that set ANSI_WARNINGS ON.
Yes, from a functional point of view it is on. However, ON or OFF will still result in different cache keys and entries. (And that is why ARITHABORT can act as placebo.)
