Set ARITHABORT Option设置为ON

MSDN注释中提到，应该总是将Set ARITHABORT Option设置为ON，原因有四：

1，如果SSMS和application client的设置不同，那么会导致application client使用不同的Query plan。

You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.

 

2，如果将SET ARITHABORT 设置为OFF，当出现Arith abort时，SQL Server使用默认值替换，可能会导致异常。

如果将SET ARITHABORT 设置为false，在执行insert，delete或update命令时，如果出现数学计算错误，溢出，被0除，SQL Server会将表达式的最终结果替换为null。在逻辑表达式，如果出现arith abort，那么逻辑表达式将返回false。

During expression evaluation when SET ARITHABORT is OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.

If SET ARITHABORT is set to OFF and an abort error occurs during the evaluation of the Boolean condition of an IF statement, the FALSE branch will be executed.

 

3，如果将SET ARITHABORT 设置为 ON，当出现Arith abort时，SQL Server将会终止语句的执行。

If SET ARITHABORT is ON , these error conditions cause the query to terminate.

 

4，在创建和更新computed index（含有computed column）或 Indexed views时，必须将Set ARITHABORT 选项设置为ON

SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

 

SET ARITHABORT { ON | OFF }[ ; ]

Terminates a query when an overflow or divide-by-zero error occurs during query execution.

The setting of SET ARITHABORT is set at execute or run time and not at parse time.

转载于:https://www.cnblogs.com/baozhu/p/8138213.html