Rounding Numerics

Question

Rounding Numerics

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
January 12, 2024 at 12:00 am

#4356911

Comments posted to this topic are about the item Rounding Numerics

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

chris.mackenzie 149 Mr or Mrs. 500 Points: 564 More actions · Answer 1

Another QOTD where the explanation would only leave the inexperienced wondering...The correct response was to indicate the setting which would give an error but the explanation contradicts the answer...

At the end of most articles is a small blurb called a person's signature which exists to provide information about how to get in touch with the person posting, including their email address, phone number, address, or where they're located. Signatures have become the graffiti of computers. People put song lyrics, pictures, philosophical quotes, even advertisements in them. (Note, however, that advertising in your signature will more often than provoke negative responses until you take it out.)

Thomas Franz Hall of Fame Points: 3894 More actions · Answer 2

With

SET ARITHABORT OFF; -- or ON, it doesn't matter 
SET NUMERIC_ROUNDABORT OFF;

I will not get a warning (contrary to the explanation). But with NUMERIC_ROUNDABORT ON I will get an arithmetic overflow error (regardless how ARITHABORT is set and regardles if the error really happens or I just add 1 + 2 without any decimal places in the two variables).

PS: Using SQL 2022 Developer.

God is real, unless declared integer.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Apologies if you don't like the explanation. Helpful critiques or comments might include what you think is wrong with the wording. The problem is an error from loss of precision, which you can suppress or allow with these settings. To correct this, you can either change a setting or adjust a datatype, however, we don't give guidance here, because there isn't a good way to say what is appropriate. It would depend on the problem domain.

The value of Arithabort does appear to matter to me because I see this on 2017/2019/2022.

-- no error
SET ARITHABORT OFF
SET NUMERIC_ROUNDABORT OFF

-- no error
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

-- error
SET ARITHABORT OFF
SET NUMERIC_ROUNDABORT ON

SET ARITHABORT ON
SET NUMERIC_ROUNDABORT ON

Entire test script included here, the NULLs in the result sets correspond to the errors. Welcome to hear from anyone that doesn't match my results. It is entirely possible there are different SET settings for me/you that affect this in some other way other than ARITHABORT or NUMERIC_ROUNDABORT.

SET ARITHABORT OFF
SET NUMERIC_ROUNDABORT OFF
GO
DECLARE @result DECIMAL(5, 2),  
   @value_1 DECIMAL(5, 4),   
   @value_2 DECIMAL(5, 4);  
SET @value_1 = 1.1234;  
SET @value_2 = 1.1234 ;  
SELECT @result = @value_1 + @value_2;  
SELECT 'Arith off, numeric off', @result;  
GO
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
GO
DECLARE @result DECIMAL(5, 2),  
   @value_1 DECIMAL(5, 4),   
   @value_2 DECIMAL(5, 4);  
SET @value_1 = 1.1234;  
SET @value_2 = 1.1234 ;  
SELECT @result = @value_1 + @value_2;  
SELECT 'Arith on, numeric oFF', @result;  
GO
SET ARITHABORT OFF
SET NUMERIC_ROUNDABORT ON
GO
DECLARE @result DECIMAL(5, 2),  
   @value_1 DECIMAL(5, 4),   
   @value_2 DECIMAL(5, 4);  
SET @value_1 = 1.1234;  
SET @value_2 = 1.1234 ;  
SELECT @result = @value_1 + @value_2;  
SELECT 'Arith off, numeric on', @result;  
GO
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT ON
GO
DECLARE @result DECIMAL(5, 2),  
   @value_1 DECIMAL(5, 4),   
   @value_2 DECIMAL(5, 4);  
SET @value_1 = 1.1234;  
SET @value_2 = 1.1234 ;  
SELECT @result = @value_1 + @value_2;  
SELECT 'Arith on, numeric on', @result;  
GO

jschmidt 17654 SSCommitted Points: 1656 More actions · Answer 4

Maybe a typo in the explanation highlighted in red below?

What setting of NUMERIC_ROUNDABORT will return an error from this code?

The correct answers are:

SET NUMERIC_ROUNDABORT ON

Explanation

When this is set to OFF, an error is returned because of the loss of precision.

From the MS info page: When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision occurs in an expression. If set to OFF, losses of precision don't generate error messages. The result is rounded to the precision of the column or variable storing the result.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 736212

January 16, 2024 at 3:02 pm

#4362677

Thanks, typo for sure. It's fixed.