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."
根据十进制MSDN文档(p,s)、p(或10在我的例子中)是“最大的小数位数总数可以存储,左边和右边的小数点”而年代(或9在我的例子中)是“最大数量的小数位数可以存储小数点右边的。”
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.
我的数字，50，只有2位数字(小于10的最大值)，小数点右边0位(小于9的最大值)，所以应该可以。
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.
我发现这个问题本质上是相同的，但是没有人解释为什么文档似乎与行为冲突。看起来s维度实际上被解释为小数点右边的固定位数，并被从p中减去，在我的例子中，p - 9 =只剩下1个数字来处理左边。
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".
对于“p(精度)”更改“可存储的十进制数字的最大总数”，以读取“将要存储的十进制数字的最大总数”。
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."
对于“s (scale)”，“更改”可以存储在小数点右边的最大小数位数。这个数字被从p中减去，以确定小数点左边的最大位数。
I'm going to submit a bug report to Connect unless some one has a better explanation.
我将提交一个bug报告来连接，除非有人有更好的解释。
5 个解决方案
#1
10
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:
10 - 9等于1。DECIMAL(10,9)可以保存格式为0.000000000的数字。50在小数点前有两位数，因此超出了范围。你自己引用:
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."
根据十进制MSDN文档(p,s)、p(或10在我的例子中)是“最大的小数位数总数可以存储,左边和右边的小数点”而年代(或9在我的例子中)是“最大数量的小数位数可以存储小数点右边的。”
#2
5
I submitted a bug report to Connect: Misleading documentation on the decimal data type
我提交了一个错误报告来连接:十进制数据类型的误导性文档
#3
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.
解释文档的一种合理的方法是，小数位数后面的小数位数没有被忽略。小数点右边有9位小数，它们都是0。
#4
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.
十进制(10,9)是一种固定的精度和刻度数值型数据类型。这意味着它总是将相同的数字存储在小数点右边。因此，您指定的数据类型只能存储小数点左边一位数字和右边9位数字的数字。显然，50不适合那种格式。
#5
1
Go though the link below. http://msdn.microsoft.com/en-gb/library/ms190476.aspx
点击下面的链接。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.
精度是数字中的位数。比例是数字中小数点右边的位数。例如，数字123.45的精度为5，刻度为2。
#1
10
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:
10 - 9等于1。DECIMAL(10,9)可以保存格式为0.000000000的数字。50在小数点前有两位数，因此超出了范围。你自己引用:
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."
根据十进制MSDN文档(p,s)、p(或10在我的例子中)是“最大的小数位数总数可以存储,左边和右边的小数点”而年代(或9在我的例子中)是“最大数量的小数位数可以存储小数点右边的。”
#2
5
I submitted a bug report to Connect: Misleading documentation on the decimal data type
我提交了一个错误报告来连接:十进制数据类型的误导性文档
#3
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.
解释文档的一种合理的方法是，小数位数后面的小数位数没有被忽略。小数点右边有9位小数，它们都是0。
#4
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.
十进制(10,9)是一种固定的精度和刻度数值型数据类型。这意味着它总是将相同的数字存储在小数点右边。因此，您指定的数据类型只能存储小数点左边一位数字和右边9位数字的数字。显然，50不适合那种格式。
#5
1
Go though the link below. http://msdn.microsoft.com/en-gb/library/ms190476.aspx
点击下面的链接。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.
精度是数字中的位数。比例是数字中小数点右边的位数。例如，数字123.45的精度为5，刻度为2。