Question regarding decimal arithmetic

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
4
down vote

favorite

I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while the decimal(18, 14) rounds to 19. My understanding of decimal is decimal(p, [s]), where p is the total number of digits and s is the number of digits after the decimal (i.g., decimal(10, 2) would result in 8 digits to the left of the decimal and 2 digits to the right). Is this not correct?

I created a small example that illustrates the seemingly odd behavior:

--------------------
-- Truncates at pipe
-- 1.043686|655...
--------------------
declare @dVal1 decimal(32, 14) = 10
declare @dVal2 decimal(32, 14) = 9.581419815465469

select @dVal1 Val1, @dVal2 Val2, @dVal1 / @dVal2 CalcResult

----------------
-- Most accurate
----------------
declare @dVal3 decimal(18, 14) = 10
declare @dVal4 decimal(18, 14) = 9.581419815465469

select @dVal3 Val3, @dVal4 Val4, @dVal3 / @dVal4 CalcResult

So on to the question, what is it that I am missing to understand this? The articles and msdn blogs I have read don't seem to provide clarity (at least to my thought process). Can someone explain to me why a higher precision seems to result in a loss of scale?

edited 4 hours ago

Anthony Genovese

1,6662723

asked 5 hours ago

Mythikos

213

New contributor

1

See this answer to help you - dba.stackexchange.com/a/41745/8783
â€“Â Kin
4 hours ago

add a commentÂ |Â

up vote
4
down vote

favorite

I created a small example that illustrates the seemingly odd behavior:

--------------------
-- Truncates at pipe
-- 1.043686|655...
--------------------
declare @dVal1 decimal(32, 14) = 10
declare @dVal2 decimal(32, 14) = 9.581419815465469

select @dVal1 Val1, @dVal2 Val2, @dVal1 / @dVal2 CalcResult

----------------
-- Most accurate
----------------
declare @dVal3 decimal(18, 14) = 10
declare @dVal4 decimal(18, 14) = 9.581419815465469

select @dVal3 Val3, @dVal4 Val4, @dVal3 / @dVal4 CalcResult

edited 4 hours ago

Anthony Genovese

1,6662723

asked 5 hours ago

Mythikos

213

New contributor

1

See this answer to help you - dba.stackexchange.com/a/41745/8783
â€“Â Kin
4 hours ago

add a commentÂ |Â

up vote
4
down vote

favorite

I created a small example that illustrates the seemingly odd behavior:

--------------------
-- Truncates at pipe
-- 1.043686|655...
--------------------
declare @dVal1 decimal(32, 14) = 10
declare @dVal2 decimal(32, 14) = 9.581419815465469

select @dVal1 Val1, @dVal2 Val2, @dVal1 / @dVal2 CalcResult

----------------
-- Most accurate
----------------
declare @dVal3 decimal(18, 14) = 10
declare @dVal4 decimal(18, 14) = 9.581419815465469

select @dVal3 Val3, @dVal4 Val4, @dVal3 / @dVal4 CalcResult

edited 4 hours ago

Anthony Genovese

1,6662723

asked 5 hours ago

Mythikos

213

New contributor

I created a small example that illustrates the seemingly odd behavior:

--------------------
-- Truncates at pipe
-- 1.043686|655...
--------------------
declare @dVal1 decimal(32, 14) = 10
declare @dVal2 decimal(32, 14) = 9.581419815465469

select @dVal1 Val1, @dVal2 Val2, @dVal1 / @dVal2 CalcResult

----------------
-- Most accurate
----------------
declare @dVal3 decimal(18, 14) = 10
declare @dVal4 decimal(18, 14) = 9.581419815465469

select @dVal3 Val3, @dVal4 Val4, @dVal3 / @dVal4 CalcResult

sql-server sql-server-2016 datatypes decimal

edited 4 hours ago

Anthony Genovese

1,6662723

asked 5 hours ago

Mythikos

213

New contributor

edited 4 hours ago

Anthony Genovese

1,6662723

asked 5 hours ago

Mythikos

213

New contributor

edited 4 hours ago

Anthony Genovese

1,6662723

edited 4 hours ago

Anthony Genovese

1,6662723

edited 4 hours ago

Anthony Genovese

1,6662723

asked 5 hours ago

Mythikos

213

New contributor

asked 5 hours ago

Mythikos

213

asked 5 hours ago

Mythikos

213

New contributor

Mythikos is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

1

See this answer to help you - dba.stackexchange.com/a/41745/8783
â€“Â Kin
4 hours ago

add a commentÂ |Â

1

See this answer to help you - dba.stackexchange.com/a/41745/8783
â€“Â Kin
4 hours ago

See this answer to help you - dba.stackexchange.com/a/41745/8783
â€“Â Kin
4 hours ago

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
3
down vote

Your understanding is correct though you have 1 too many digits for @dVal2 and @dVal4 which is why you see these being rounded up to 7 (9.58141981546547) for the last digit in your select

As for the division rounding, it's hidden in the middle of the docs.

In multiplication and division operations we need precision - scale
places to store the integral part of the result. The scale might be
reduced using the following rules:

The resulting scale is reduced to min(scale, 38 Ã¢Â€Â“ (precision-scale))
if the integral part is less than 32, because it cannot be greater
than 38 Ã¢Â€Â“ (precision-scale). Result might be rounded in this case.

The
scale will not be changed if it is less than 6 and if the integral
part is greater than 32. In this case, overflow error might be raised
if it cannot fit into decimal(38, scale)

The scale will be set to 6 if
it is greater than 6 and if the integral part is greater than 32. In
this case, both integral part and scale would be reduced and resulting
type is decimal(38,6). Result might be rounded to 6 decimal places or
overflow error will be thrown if integral part cannot fit into 32
digits.

So in your first case decimal(32,14), the scale is being set to 6 digits because the resulting value decimal(64,28) has a scale = 28 which is > 6 and the integral part (64-28) = 36 is > 32 as defined in the last rule above. Thus, decimal(38,6)

In your second case decimal(18,14), the first rule is being applied for your scale for the resulting value of decimal(36,28) to min(28, 38 -(36-28)) = min(28,30) = 28. Thus, decimal(38,28)

edited 4 hours ago

answered 5 hours ago

scsimon

969311

1

Ah I see, that makes sense. I didnt see the 3 additional conditions down there on that doc, thanks for pointing that out! A follow up question, how did you determine the new precision/scale of 64,28? That doesnt appear to follow the rules on the doc you linked. Working it out myself based on the doc, I came to the precision of 51 and the scale of 47. Am I doing something goofy in my math? precision = 32 - 14 + 14 + max(6, 32 + 14 + 1) = 32 - 28 + 47 = 51 scale = max(6, 32 + 14 + 1) = 47
â€“Â Mythikos
2 hours ago

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Mythikos is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220165%2fquestion-regarding-decimal-arithmetic%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
3
down vote

Your understanding is correct though you have 1 too many digits for @dVal2 and @dVal4 which is why you see these being rounded up to 7 (9.58141981546547) for the last digit in your select

As for the division rounding, it's hidden in the middle of the docs.

In multiplication and division operations we need precision - scale
places to store the integral part of the result. The scale might be
reduced using the following rules:

The resulting scale is reduced to min(scale, 38 Ã¢Â€Â“ (precision-scale))
if the integral part is less than 32, because it cannot be greater
than 38 Ã¢Â€Â“ (precision-scale). Result might be rounded in this case.

The
scale will not be changed if it is less than 6 and if the integral
part is greater than 32. In this case, overflow error might be raised
if it cannot fit into decimal(38, scale)

The scale will be set to 6 if
it is greater than 6 and if the integral part is greater than 32. In
this case, both integral part and scale would be reduced and resulting
type is decimal(38,6). Result might be rounded to 6 decimal places or
overflow error will be thrown if integral part cannot fit into 32
digits.

In your second case decimal(18,14), the first rule is being applied for your scale for the resulting value of decimal(36,28) to min(28, 38 -(36-28)) = min(28,30) = 28. Thus, decimal(38,28)

edited 4 hours ago

answered 5 hours ago

scsimon

969311

1

Ah I see, that makes sense. I didnt see the 3 additional conditions down there on that doc, thanks for pointing that out! A follow up question, how did you determine the new precision/scale of 64,28? That doesnt appear to follow the rules on the doc you linked. Working it out myself based on the doc, I came to the precision of 51 and the scale of 47. Am I doing something goofy in my math? precision = 32 - 14 + 14 + max(6, 32 + 14 + 1) = 32 - 28 + 47 = 51 scale = max(6, 32 + 14 + 1) = 47
â€“Â Mythikos
2 hours ago

add a commentÂ |Â

up vote
3
down vote

Your understanding is correct though you have 1 too many digits for @dVal2 and @dVal4 which is why you see these being rounded up to 7 (9.58141981546547) for the last digit in your select

As for the division rounding, it's hidden in the middle of the docs.

In multiplication and division operations we need precision - scale
places to store the integral part of the result. The scale might be
reduced using the following rules:

The resulting scale is reduced to min(scale, 38 Ã¢Â€Â“ (precision-scale))
if the integral part is less than 32, because it cannot be greater
than 38 Ã¢Â€Â“ (precision-scale). Result might be rounded in this case.

The
scale will not be changed if it is less than 6 and if the integral
part is greater than 32. In this case, overflow error might be raised
if it cannot fit into decimal(38, scale)

The scale will be set to 6 if
it is greater than 6 and if the integral part is greater than 32. In
this case, both integral part and scale would be reduced and resulting
type is decimal(38,6). Result might be rounded to 6 decimal places or
overflow error will be thrown if integral part cannot fit into 32
digits.

In your second case decimal(18,14), the first rule is being applied for your scale for the resulting value of decimal(36,28) to min(28, 38 -(36-28)) = min(28,30) = 28. Thus, decimal(38,28)

edited 4 hours ago

answered 5 hours ago

scsimon

969311

1

Ah I see, that makes sense. I didnt see the 3 additional conditions down there on that doc, thanks for pointing that out! A follow up question, how did you determine the new precision/scale of 64,28? That doesnt appear to follow the rules on the doc you linked. Working it out myself based on the doc, I came to the precision of 51 and the scale of 47. Am I doing something goofy in my math? precision = 32 - 14 + 14 + max(6, 32 + 14 + 1) = 32 - 28 + 47 = 51 scale = max(6, 32 + 14 + 1) = 47
â€“Â Mythikos
2 hours ago

add a commentÂ |Â

up vote
3
down vote

Your understanding is correct though you have 1 too many digits for @dVal2 and @dVal4 which is why you see these being rounded up to 7 (9.58141981546547) for the last digit in your select

As for the division rounding, it's hidden in the middle of the docs.

In multiplication and division operations we need precision - scale
places to store the integral part of the result. The scale might be
reduced using the following rules:

The resulting scale is reduced to min(scale, 38 Ã¢Â€Â“ (precision-scale))
if the integral part is less than 32, because it cannot be greater
than 38 Ã¢Â€Â“ (precision-scale). Result might be rounded in this case.

The
scale will not be changed if it is less than 6 and if the integral
part is greater than 32. In this case, overflow error might be raised
if it cannot fit into decimal(38, scale)

The scale will be set to 6 if
it is greater than 6 and if the integral part is greater than 32. In
this case, both integral part and scale would be reduced and resulting
type is decimal(38,6). Result might be rounded to 6 decimal places or
overflow error will be thrown if integral part cannot fit into 32
digits.

In your second case decimal(18,14), the first rule is being applied for your scale for the resulting value of decimal(36,28) to min(28, 38 -(36-28)) = min(28,30) = 28. Thus, decimal(38,28)

edited 4 hours ago

answered 5 hours ago

scsimon

969311

Your understanding is correct though you have 1 too many digits for @dVal2 and @dVal4 which is why you see these being rounded up to 7 (9.58141981546547) for the last digit in your select

As for the division rounding, it's hidden in the middle of the docs.

In multiplication and division operations we need precision - scale
places to store the integral part of the result. The scale might be
reduced using the following rules:

The resulting scale is reduced to min(scale, 38 Ã¢Â€Â“ (precision-scale))
if the integral part is less than 32, because it cannot be greater
than 38 Ã¢Â€Â“ (precision-scale). Result might be rounded in this case.

The
scale will not be changed if it is less than 6 and if the integral
part is greater than 32. In this case, overflow error might be raised
if it cannot fit into decimal(38, scale)

The scale will be set to 6 if
it is greater than 6 and if the integral part is greater than 32. In
this case, both integral part and scale would be reduced and resulting
type is decimal(38,6). Result might be rounded to 6 decimal places or
overflow error will be thrown if integral part cannot fit into 32
digits.

In your second case decimal(18,14), the first rule is being applied for your scale for the resulting value of decimal(36,28) to min(28, 38 -(36-28)) = min(28,30) = 28. Thus, decimal(38,28)

edited 4 hours ago

answered 5 hours ago

scsimon

969311

edited 4 hours ago

answered 5 hours ago

scsimon

969311

answered 5 hours ago

scsimon

969311

answered 5 hours ago

scsimon

969311

1

Ah I see, that makes sense. I didnt see the 3 additional conditions down there on that doc, thanks for pointing that out! A follow up question, how did you determine the new precision/scale of 64,28? That doesnt appear to follow the rules on the doc you linked. Working it out myself based on the doc, I came to the precision of 51 and the scale of 47. Am I doing something goofy in my math? precision = 32 - 14 + 14 + max(6, 32 + 14 + 1) = 32 - 28 + 47 = 51 scale = max(6, 32 + 14 + 1) = 47
â€“Â Mythikos
2 hours ago

add a commentÂ |Â

1

Ah I see, that makes sense. I didnt see the 3 additional conditions down there on that doc, thanks for pointing that out! A follow up question, how did you determine the new precision/scale of 64,28? That doesnt appear to follow the rules on the doc you linked. Working it out myself based on the doc, I came to the precision of 51 and the scale of 47. Am I doing something goofy in my math? precision = 32 - 14 + 14 + max(6, 32 + 14 + 1) = 32 - 28 + 47 = 51 scale = max(6, 32 + 14 + 1) = 47
â€“Â Mythikos
2 hours ago

Ah I see, that makes sense. I didnt see the 3 additional conditions down there on that doc, thanks for pointing that out! A follow up question, how did you determine the new precision/scale of 64,28? That doesnt appear to follow the rules on the doc you linked. Working it out myself based on the doc, I came to the precision of 51 and the scale of 47. Am I doing something goofy in my math? precision = 32 - 14 + 14 + max(6, 32 + 14 + 1) = 32 - 28 + 47 = 51 scale = max(6, 32 + 14 + 1) = 47
â€“Â Mythikos
2 hours ago

add a commentÂ |Â

Mythikos is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Mythikos is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky