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?

up vote 3 down vote accepted

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)

  • 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 Oct 15 at 23:24
  • I think I goofed it up actually @Mythikos – scsimon Oct 16 at 13:14
  • ahh alright. Regardless, thank you for the help! – Mythikos Oct 16 at 20:20

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.