INF: Using the NUMERIC_ROUNDABORT SET Option

Last reviewed: May 1, 1997
Article ID: 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 : kbprg kbusage SSrvProg SSrvTran_SQL
Version : 6.0
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 1, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.