Today, an index is adjusted. To improve the performance, the index is changed to filteredindex. the following error occurs in a Java program:
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 configurations and/or XML data type methods and/orspatial index operations.
If a proifilertrace is caught, the statement that indicates an error is a common DML insert statement. If you put the statement in SSMs, it can be executed normally, but it will always fail in the program. No way to fliter
The index deletion program returns to normal.
Later I found that the filter index was used. Make sure that some set options are correct:
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 |
* Unable 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
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.
Therefore, you must be cautious when using the filter index, and put it in the formal environment after full testing.
In fact, it is recommended that arithabort be set to enable. It is enabled by default (refer to the following section). I don't know why developers should set it to off.
Http://msdn.microsoft.com/zh-cn/library/ms190306.aspx