Microsoft KB Archive/240118

{|
 * width="100%"|

INF: ARITHABORT and ARITHIGNORE Behave Different on 6.5 and 7.0 When Handling Modification

 * }

-

The information in this article applies to:


 * Microsoft SQL Server versions 6.5, 7.0

-

SUMMARY
The insert and update behaviors for different ARITHABORT, ARITHIGNORE, and ANSI_WARNINGS settings are handled very differently between Microsoft SQL Server 6.5 and 7.0. You must make sure that you understand the differences or otherwise you may get unexpected results when performing insert and update, especially on Microsoft SQL Server 6.5 releases.

On Microsoft SQL Server 6.5, when you INSERT an overflow or divide-by-zero value to a column that allows NULL, and if neither the ANSI_WARNINGS nor ARITHABORT is set, a NULL value will be inserted onto the column. Note that an ARITHIGNORE setting doesn't have any effect to prevent the NULL value being inserted. This situation is also true for UPDATE statement.

However, on Microsoft SQL Server 7.0, no matter what the ANSI settings are, a NULL value does not get INSERTed into the table when an overflow or divide-by-zero occurs. This guarantees that we do not get any unexpected result due to an arithmetic overflow or divide-by-zero occurred on an insert.

Also, the Microsoft SQL 7.0 BOL topic: INSERT (T-SQL) documents the INSERT behavior as "When an INSERT statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, Microsoft SQL Server handles these errors as if SET ARITHABORT is ON. The remainder of the batch is halted, and an error message is returned."

MORE INFORMATION
The following examples can illustrate the problem that occurs on Microsoft SQL Server 6.5.

Example 1:
  create table mytest (a decimal(2,2) NULL,   b int          NOT NULL) go  insert into mytest values(12.34, 9) go  insert into mytest values(1/0, 8) go  select  * from mytest

Results:
  Arithmetic overflow occurred.

(1 row(s) affected)

Divide by zero occurred.

(1 row(s) affected)

a     b              -- --- (null) 9 (null) 8

(2 row(s) affected)

Example 2:
  truncate table mytest go  -- set ANSI_WARNINGS ON can avoid NULL is inserted under this situation SET ANSI_WARNINGS ON        go   insert into mytest values(1/0, 9) go  select * from mytest

Results:
  Msg 8134, Level 16 State 1 Divide by zero error encountered Command has been aborted

a     b              -- ---

(0 row(s) affected) On Microsoft SQL Server 7.0, no matter what the ANSI settings are, Microsoft SQL Server 7.0 does not INSERT NULL values in a null column when arithmetic overflow or divide-by-zero occurs.

Additional query words:

Keywords : SSrvTran_SQL kbSQLServ650 kbSQLServ700 kbDSupport kbinfo

Version : winnt:6.5,7.0

Platform : winnt

Issue type : kbinfo

Technology :