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

• 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) 

Additional query words:

Keywords : SSrvTran_SQL kbSQLServ650 kbSQLServ700 kbDSupport kbinfo
Version : winnt:6.5,7.0
Platform : winnt
Issue type : kbinfo