7

This one is pretty straightforward. Why does the code below cause the error below?

declare @dTest decimal(10, 9)
set @dTest = 50

Error:

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting int to data type numeric.

According to the MSDN documentation on decimal(p, s), p (or 10 in my case) is the "maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point" whereas s (or 9 in my case) is the "maximum number of decimal digits that can be stored to the right of the decimal point."

My number, 50, has only 2 digits total (which less than the maximum 10), and 0 digits to the right of the decimal (which is less than the maximum 9), therefore it should work.

I found this question about essentially the same issue, but no one explained why the documentation seems to conflict with the behavior. It seems like the s dimension is actually being interpreted as the fixed number of digits to the right of the decimal, and being subtracted from the p number, which in my case leaves 10 - 9 = only 1 digit remaining to handle the left side.

Can anyone provide a reasonable way to interpret the documentation as written to match the behavior?

EDIT:

I see some explanations below, but they don't address the fundamental problem with the wording of the docs. I would suggest this change in wording:

For "p (precision)" change "The maximum total number of decimal digits that can be stored" to read "The maximum total number of decimal digits that will be stored".

And for "s (scale)" change "The maximum number of decimal digits that can be stored to the right of the decimal point." to "The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point."

I'm going to submit a bug report to Connect unless some one has a better explanation.

Community
  • 1
  • 1
Jordan Rieger
  • 3,025
  • 3
  • 30
  • 50
  • MS responded to my bug report, and has now updated the documentation for SQL Server 2012 as I suggested. See https://connect.microsoft.com/SQLServer/feedback/details/760495/. Take that, downvoters! :P – Jordan Rieger Sep 18 '13 at 22:37

5 Answers5

12

10 - 9 is 1. DECIMAL(10, 9) can hold a number in the format 0.000000000. 50 has two digits before the decimal point, and is therefore out of range. You quoted it yourself:

According to the MSDN documentation on decimal(p, s), p (or 10 in my case) is the "maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point" whereas s (or 9 in my case) is the "maximum number of decimal digits that can be stored to the right of the decimal point."

Ry-
  • 218,210
  • 55
  • 464
  • 476
  • 2
    "DECIMAL(10, 9) can hold a number in the format 0.000000000." How does that follow from the documentation? It doesn't talk about subtracting *s* from *p*, it talks about the *maximum* digits storable in total, and on the right side. Since 50 has none on the right side, it should follow that all 10 remaining *total* digits can be used on the left side. – Jordan Rieger Aug 31 '12 at 00:05
  • @JordanRieger: Nope, you specified that the precision is 9 to the left so it will be 9 to the left and 1 to the right. It doesn't vary. – Ry- Aug 31 '12 at 00:07
  • 1
    I think you mean 9 to the *right*. Where does the doc say that (10, 9) means that there are *always* 9 digits to the right? It says only that the *maximum* to the right is 9. – Jordan Rieger Aug 31 '12 at 00:07
  • @JordanRieger: A *slight* wording error then. (And yeah, I meant right/right/left. Sorry.) – Ry- Aug 31 '12 at 00:14
  • 2
    Yes, I agree it's a *slight* wording error. But the reason I think it should be corrected is that if interpreted literally I can (and did) get unexpected results. Software docs should be written for pedantic, literal-minded programmers :) – Jordan Rieger Aug 31 '12 at 00:18
  • @JordanRieger: You're right, but you can figure out what they mean from the definition of `DECIMAL`, too: *"Fixed precision and scale numbers."* – Ry- Aug 31 '12 at 00:20
  • Well, sort of. The numbers are fixed. But if the numbers are defined as maximums, that just means the maximums are fixed. Which means the actual number of digits could still vary. – Jordan Rieger Aug 31 '12 at 00:35
7

I submitted a bug report to Connect: Misleading documentation on the decimal data type

Jordan Rieger
  • 3,025
  • 3
  • 30
  • 50
  • 1
    Oh and to those voting me down, please read a little more closely. Two experts above have both admitted in the comments below their answers that this is an error or omission in the docs. This is not the first time I've found errors in MSDN where the precise wording doesn't match the behavior of the language. The last time was only a few months ago, and not only did Jon Skeet agree, but an MS employee helped push along the bug report on Connect, and got the docs changed: http://stackoverflow.com/questions/9689839/msdn-documentation-error-if-the-procedure-is-shared-all-its-local-variables-a. – Jordan Rieger Aug 31 '12 at 05:28
  • 2
    MS responded to my bug report, and has now updated the documentation for SQL Server 2012 as I suggested. https://connect.microsoft.com/SQLServer/feedback/details/760495/. Take that, downvoters :-) – Jordan Rieger Sep 18 '13 at 22:36
  • 2
    I am upvoting to negate some of the downvotes, because the documentation *is* wrong and the original question and this answer are right on pointing that out. – G. Stoynev Dec 16 '13 at 20:24
2

A reasonable way to interpret the documentation is that trailing decimal zero digits are not ignored. So your number has 9 decimal digits to the right of the decimal point, and they all happen to be 0.

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285
  • Nope, because there could be an infinite amount of trailing zero digits. What is there to say that the number "50" has 9 or 999 trailing zero's? – Jordan Rieger Aug 31 '12 at 00:03
  • The `s` parameter in your `DECIMAL(p, s)` type specifies exactly how many trailing digits are stored in the representation. – Greg Hewgill Aug 31 '12 at 00:04
  • No, according to the docs it specifies the *maximum* amount of of trailing digits. Please understand, I'm not questioning the behavior of SQL Server but rather questioning the wording of the docs. – Jordan Rieger Aug 31 '12 at 00:06
  • To split hairs, the documentation only says anything about the maximum number of decimal digits to the right. It doesn't say anything about the minimum. Perhaps that is an omission in the documentation. – Greg Hewgill Aug 31 '12 at 00:10
1

DECIMAL(10, 9) is a fixed precision and scale numeric data type. This means that it always stores the same number of digits to the right of the decimal point. So the data type you specified can only store numbers with one digit to the left of the decimal point and 9 digits to the right. Obviously, 50 does not fit in a number of that format.

murgatroid99
  • 19,007
  • 10
  • 60
  • 95
1

Go though the link below. http://msdn.microsoft.com/en-gb/library/ms190476.aspx

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

user2176742
  • 19
  • 1
  • Unfortunately that definition conflicts with http://msdn.microsoft.com/en-us/library/ms187746(v=sql.110).aspx, which uses the word "maximum", which is why two separate Microsoft employees accepted my submission to get the misleading documentation changed. See https://connect.microsoft.com/SQLServer/feedback/details/760495/misleading-documentation-on-the-decimal-data-type. All they need to do is remove the word "maximum" and change "can" to "will" in the "s" definition, and add the other sentence I mentioned in my original question. Looks like they just haven't gotten around to it yet :) – Jordan Rieger Mar 16 '13 at 15:54