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