Mixing
Question regarding decimal arithmetic
Clash Royale CLAN TAG#URR8PPP
.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?
sql-server sql-server-2016 datatypes decimal
edited 4 hours ago
Anthony Genovese
1,6662723
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.
add a comment |Â
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?
sql-server sql-server-2016 datatypes decimal
edited 4 hours ago
Anthony Genovese
1,6662723
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 |Â
up vote
4
down vote
favorite
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?
sql-server sql-server-2016 datatypes decimal
edited 4 hours ago
Anthony Genovese
1,6662723
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.
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?
sql-server sql-server-2016 datatypes decimal
sql-server sql-server-2016 datatypes decimal
edited 4 hours ago
Anthony Genovese
1,6662723
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.
edited 4 hours ago
Anthony Genovese
1,6662723
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.
edited 4 hours ago
Anthony Genovese
1,6662723
edited 4 hours ago
Anthony Genovese
1,6662723
edited 4 hours ago
Anthony Genovese
1,6662723
1,6662723
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.
asked 5 hours ago
Mythikos
213
asked 5 hours ago
Mythikos
213
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.
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.
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
1
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)
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 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
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)
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.
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)
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
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)
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.
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)
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
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
1
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.
Mythikos is a new contributor. Be nice, and check out our Code of Conduct.
Mythikos is a new contributor. Be nice, and check out our Code of Conduct.
Mythikos is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
See this answer to help you - dba.stackexchange.com/a/41745/8783
– Kin
4 hours ago