INF: Using the NUMERIC_ROUNDABORT SET Option

• Microsoft SQL Server, version 6.0

SUMMARY

Microsoft SQL Server version 6.0 has a new SET option to abort the truncation of numeric values. The new option NUMERIC_ROUNDABORT specifies the behavior following a loss of scale for an exact numeric type during conversion. By default this option is OFF, allowing SQL Server to round the numeric result and continue processing. When this option is ON, SQL Server aborts the statement or query that caused the error.

This behavior was previously covered under the SET ARITHABORT option, which did not allow you to abort the loss of most significant digits and ignore the loss of least significant digits.

MORE INFORMATION

The SET NUMERIC_ROUNDABORT option does not allow the least significant digits (or the most significant digits) to be lost for an exact numeric type during conversion. For example, an attempt to insert a value with three digits after the decimal into a numeric datatype with scale two will complete successfully with the default setting NUMERIC_ROUNDABORT OFF, as in the following script:

   use pubs
   go
   create table t1 (x numeric(6,2))
   go
   insert t1 values (1234.567)
   go

   use pubs
   go
   set numeric_roundabort on
   create table t2 ( x numeric(6,2))
   go
   insert t2 values (1234.567)
   go