本文共 1357 字，大约阅读时间需要 4 分钟。
今天对一个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大家一定要谨慎，而且要经过充分的测试之后再放到正式环境。
本文转自 lzf328 51CTO博客，原文链接:
http://blog.51cto.com/lzf328/1092989
转载地址：http://ysial.baihongyu.com/