Arithabort and Set Ansi warning defaults

Question

Arithabort and Set Ansi warning defaults

Rin Sitah

SSCrazy

Points: 2180
More actions
April 21, 2016 at 3:01 am

#315892

Hi There,
Im a bit puzzled by how sql is behaving and am wondering if someone can shed some light on this.
The default on our database for arithabort and set ansi warnings is OFF.
When I run a select from sys.databases, the database shows up with is_ansi_warnings_on = 0 and is_arithabort_on = 0.
However, if I run a select 1/0, it returns a divide by zero error.
Further, if I look at the @@Options, I can see that Arithabort and ansiwarnings are set to ON.
Why are they set to ON, if the default on the database is set for them to be off?
We're on 12.0.2000.8

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Rin Sitah (4/21/2016)
Hi There,
Im a bit puzzled by how sql is behaving and am wondering if someone can shed some light on this.
The default on our database for arithabort and set ansi warnings is OFF.
When I run a select from sys.databases, the database shows up with is_ansi_warnings_on = 0 and is_arithabort_on = 0.
However, if I run a select 1/0, it returns a divide by zero error.
Further, if I look at the @@Options, I can see that Arithabort and ansiwarnings are set to ON.
Why are they set to ON, if the default on the database is set for them to be off?
We're on 12.0.2000.8

If you're running the query from SSMS, it has it's own connection properties that override the database defaults.

Shifting gears, I don't see the utility in wanting to allow a SELECT 1/0 to occur without an error also occurring. My personal opinion is that should be handled properly rather than relying on some database-wide setting that also affects other things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rin Sitah SSCrazy Points: 2180 More actions · Answer 2

Thanks Jeff,

The reason we wanted these options was due a view that was being queried via Excel. We wanted the view to return rows even if there was an error on some of them, and setting arithabort in a view is not supported.

In the end we have just added NULLIF on the views division columns to handle any divide by zeros instead of using SET Arithabort off.

Many thanks.