Home > SQL Server > Preventing numeric rounding data loss – SET Numeric_RoundAbort – SQL Server

Preventing numeric rounding data loss – SET Numeric_RoundAbort – SQL Server


NUMERIC_ROUNDABORT : This set statement configure error event for the current session to avoid numeric value rounding data loss. If we set numeric_roundabort to on then SQL server session returns an error message after a loss of precision occurs and if it is off then it returns the rounded value with data loss.

We can also use SET ARITHABORT along with NUMERIC_ROUNDABORT.

Usage

SET NUMERIC_ROUNDABORT ON
or
SET NUMERIC_ROUNDABORT OFF
 

Example

1. Set numeric_roundabort to on. In this example, we are trying to store a numeric value which is larger than the declared data type.

pic1It throws and error and return a null value.

2. Now, we are setting numeric_roundabort to off. This time we will get an output with data loss without error.

pic2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: