Risks of changing to ARITHABORT ON

I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application. It's built in ColdFusion connecting to a SQL Server 2005 database.

Some of the reports I've built depend on views using functions computed from the core tables, and the reports are getting very slow as the tables get larger. To speed up the reports, I want to use indexed views. But after creating an indexed view in my test environment, the core application could no longer insert into the core tables (it returned an error message that ARITHABORT is required to be ON when using indexed views).

So it seems that in order to use indexed views, I need to have the core application SET ARITHABORT ON whenever inserting/updating the core tables. I ran this in my test environment:

ALTER DATABASE MyDatabase SET ARITHABORT ON;

and it seems to work fine. But my vendor says since the application has thousands of queries, there could be a risk that this setting could break one of these queries, and if we have some future unexpected database issue they would insist I restore the default setting.

Are there actual queries that would be broken by SET ARITHABORT ON? Is there any situation where it would be better to keep it OFF?

TL;DR For my new indexed views to work I need to set ARITHABORT ON for the whole database, but my vendor warns it will be at my own risk. Is there actually a risk?

So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more details on why they are concerned here.

Of all of the rules of indexed views, I would call this and many of the set options rules the least controversial.

This would have to be set in the connections that interact with the view. So you would want to work with the vendor and really try and understand their reasoning and try and get them to commit to what they are thinking on the big disagreement here.

That said - Indexed views are a bit of a big deal. They have other rules and they can impact the application and assumptions the vendor's developers had when building and performance testing. You should really have a conversation with them about the business problem you are trying to solve through indexed views and get them involved in the conversation about how to solve the problem.

StackExchange Database Administrators Q#30132, answer score: 9