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.

 

參考doc：

SET ARITHABORT (Transact-SQL)

Set ARITHABORT Option設定為ON