Misleading documentation on the decimal data type - by Jordan Rieger

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 760495 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/30/2012 5:27:53 PM
Access Restriction Public

Description

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][1] 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 is 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.

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.

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."

  [1]: http://msdn.microsoft.com/en-us/library/ms187746%28v=sql.100%29.aspx
  [2]: http://stackoverflow.com/questions/12207222/decimal-10-9-variable-cant-hold-the-number-50-sql-server-2008
Sign in to post a comment.
Posted by Microsoft on 7/18/2013 at 6:06 PM
Hi Jordan,
Sorry about the delay. The topic in SQL Server 2012 has been corrected and will be published the week of July 23, 2013. The equivalent topic in SQL Server 2014 will not be published for a few months, but it has also been fixed.

Regards,
Gail Erickson
Posted by Microsoft on 10/26/2012 at 12:49 PM
Thank you for reporting this error. We will review the topic and update it as appropriate.
Kind Regards,
Gail Erickson
SQL Server Documentation Team
Posted by SteveH_UK on 9/6/2012 at 9:01 AM
Agree. Documentation is misleading, but low priority for me as most people already know this. Should be fixed, regardless.