Microsoft KB Archive/136468

{|
 * width="100%"|

INF: Using the NUMERIC_ROUNDABORT SET Option

 * }

Q136468

-

The information in this article applies to:


 * 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 With the option NUMERIC_ROUNDABORT ON, the insert will abort, as in the following script:

  use pubs go  set numeric_roundabort on   create table t2 ( x numeric(6,2)) go  insert t2 values (1234.567) go Additional query words: sql6 convert truncate

Keywords : kbprogramming kbusage

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600