十进制 (10,9) 变量不能容纳数字 50 (SQL Server 2008) [英] Decimal (10,9) variable can't hold the number 50 (SQL Server 2008)

问题描述

这个很简单.为什么下面的代码会导致下面的错误?

声明@dTest十进制(10, 9)设置@dTest = 50

错误:

Msg 8115, Level 16, State 8, Line 3将 int 转换为数据类型 numeric 时出现算术溢出错误.

根据 MSDN 文档 在 decimal(p, s) 上，p(或在我的情况下为 10)是可以存储的最大十进制数字总数，两者在小数点的左侧和右侧"，而 s(或在我的情况下为 9)是最大十进制数字，可以是存储在小数点右侧."

我的号码，50，总共只有 2 位(小于 最大 10)，小数点右边有 0 位(小于最大em> 9)，因此它应该可以工作.

我发现了这个关于本质上相同的问题的问题，但没有人解释为什么文档似乎与行为冲突.看起来 s 维度实际上被解释为 固定 小数点右边的数字位数，并从 减去em>p 数字，在我的情况下，剩下 10 - 9 = 只剩下 1 位数字来处理左侧.

任何人都可以提供一种合理的方式来解释与行为相匹配的文档吗?

我在下面看到了一些解释，但它们没有解决文档措辞的根本问题.我建议更改措辞:

对于p(精度)"，将可以存储的十进制数字的最大总数"更改为将存储的十进制数字的最大总数".

并将s(小数位数)"更改为小数点右侧可以存储的最大小数位数".到将存储在小数点右侧的小数位数.这个数字从 p 中减去以确定小数点左侧的最大位数.">

除非有人有更好的解释，否则我将向 Connect 提交错误报告.

解决方案

我向 Connect 提交了错误报告:关于十进制数据类型的误导性文档

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.

解决方案

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

这篇关于十进制 (10,9) 变量不能容纳数字 50 (SQL Server 2008)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！