Error: 1934 INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.

今天对一个Index做了调整，为了提高性能将Index修改为FilteredIndex,结果一个JAVA程序出现了下面的错误：

 

Error: 1934, Severity: 16, State: 1

 

INSERT failed because thefollowing SET options have incorrect settings: 'ARITHABORT'.  Verify that SET options are correct for use withindexed views and/or indexes

 on computed columns and/or filtered indexes and/orquery notifications and/or XML data type methods  and/orspatial index operations.

 

抓了一个ProifilerTrace看到出错的语句就是普通的DML插入语句，而且将语句放到SSMS中可以正常执行，但是在程序中一直失败。没有办法将Fliter Index删除程序恢复正常。

 

后来查到使用Filter Index，要保证一些SET选项正确：

 

SET options	Required value
ANSI_NULLS	ON
ANSI_PADDING	ON
ANSI_WARNINGS*	ON
ARITHABORT	ON
CONCAT_NULL_YIELDS_NULL	ON
NUMERIC_ROUNDABORT	OFF
QUOTED_IDENTIFIER	ON

 

*SettingANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the databasecompatibility level is set to 90 or higher. If the database compatibility levelis set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

 

If the SET options areincorrect, the following conditions can occur:

·        The filtered index isnot created.

·        The Database Enginegenerates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statementsthat change data in the index.

·        Query optimizer does notconsider the index in the execution plan for any Transact-SQL statements.

 

所以使用Filter Index大家一定要谨慎，而且要经过充分的测试之后再放到正式环境。

 

其实ARITHABORT微软建议是设为开启，默认为开启（参考下文），不知道开发人员为什么要设置为OFF。

http://msdn.microsoft.com/zh-cn/library/ms190306.aspx