SQL数值数据类型截断值？ [英] SQL Numeric data type truncating value?
问题描述
我真的希望那里的一些SQL专家可以帮助解决这个问题（如果以前已经回答过，我也道歉。我确实尝试过找到类似的帖子，但无济于事）：
I really hope some SQL guru out there can assist with this one (and my apologies if this has been answered before. I did try and find a similar post but to no avail):
declare @theanswer numeric(38,16)
select @theanswer = 0.01 / 0.0074464347
select @theanswer
以上结果为1.3429245542165000
The above results in 1.3429245542165000
，但以下内容（其中
declare @val1 numeric(38,16);
declare @val2 numeric(38,16);
set @val1 = 0.01;
set @val2 = 0.0074464347;
select @val1/@val2
结果为1.342924并截断吗？
results with 1.342924 and truncates it?
有什么想法吗？
推荐答案
获得真正的精度和规模对于结果（@ val1 / @ val2），我将执行此 T-SQL 脚本
To get the real precision and scale for the result (@val1/@val2) I would execute this T-SQL script
DECLARE @val1 NUMERIC(38,16);
DECLARE @val2 NUMERIC(38,16);
SET @val1 = 0.01;
SET @val2 = 0.0074464347;
SELECT @val1/@val2
,SQL_VARIANT_PROPERTY(@val1/@val2, 'BaseType') [BaseType]
,SQL_VARIANT_PROPERTY(@val1/@val2, 'Precision') [Precision]
,SQL_VARIANT_PROPERTY(@val1/@val2, 'Scale') [Scale]
结果将是：
(No column name) BaseType Precision Scale
1.342924 numeric 38 6
因此，结果精度为38，结果范围为6。
MSDN中有一些有关算术运算的精度和小数位数。这些信息可以在这里找到：精度，小数位和长度（Transact-SQL）：
So, the result precision is 38 and result scale is 6. MSDN has some details regarding precision and scale for arithmetic operations. These information can be found here: Precision, Scale, and Length (Transact-SQL):
Operation = e1 / e2
Result precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Result scale * = max(6, s1 + p2 + 1)
使用这些公式，我们可以编写下一个 T-SQL 脚本来获得理论精度和结果范围（@ val1 / @ val2）：
Using these formulas we can write the next T-SQL script to get theoretical precision and scale for the result (@val1/@val2) :
SELECT @p1 = 38 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val1, 'Precision'))
,@s1 = 16 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val1, 'Scale'))
,@p2 = 38 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val2, 'Precision'))
,@s2 = 16 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val2, 'Scale'));
--SELECT @p1 [@p1], @s1 [@s1], @p2 [@p2], @s2 [@s2];
SELECT @p_result = @p1 - @s1 + @s2 + CASE
WHEN 6 >= @s1 + @p2 + 1 THEN 6
WHEN 6 < @s1 + @p2 + 1 THEN @s1 + @p2 + 1
END
,@s_result = CASE
WHEN 6 >= @s1 + @p2 + 1 THEN 6
WHEN 6 < @s1 + @p2 + 1 THEN @s1 + @p2 + 1
END;
SELECT @p_result [@p_result], @s_result [@s_result];
结果是：
@p_result @s_result
93 55
因此，为此算术运算（@ val1 / @ val2），在理论中，精度和小数位数为 93 和 55 ，但是真正的精度和小数位数是 38 和 6 。
实际精度为38，因为 结果精度和标度的绝对最大值为38 。
So, for this arithmetic operation (@val1/@val2), in theory the precision and scale are 93 and 55 but the real precision and scale are 38 and 6.
The real precision is 38 because "The result precision and scale have an absolute maximum of 38".
关于实际结果范围（6）MSDN，尚不清楚：当结果精度大于38时，相应的范围将减小，以防止结果的整数部分被截断。
Regarding the real result scale (6) MSDN it's not clear: "When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated".
要查看相应比例是如何减小的，我执行了上述测试（脚本1表示实际精度和比例，脚本2表示理论精度和比例）使用具有相同比例（16）但不同比例（从16到38）的 NUMERIC 值。这些测试的结果为：
To see how "the corresponding scale is reduced" I executed the above tests (script 1 for real precision and scale and script 2 for theoretical precision and scale) using NUMERIC values having the same scale (16) but different scales (from 16 to 38). The results of these tests are:
/*
Result prec. Result scale (T=theoretical value, R=real value)
T-R T-R --@val1 and @val2 data type
49-38 33-22 --NUMERIC(16, 16)
51-38 34-21 --NUMERIC(17, 16)
53-38 35-20 --NUMERIC(18, 16)
55-38 36-19 --NUMERIC(19, 16)
...
61-38 39-16 --NUMERIC(22, 16) -- <-- data type for [real] result scale 16
...
77-38 47-8 --NUMERIC(30, 16)
79-38 48-7 --NUMERIC(31, 16)
81-38 49-6 --NUMERIC(32, 16)
83-38 50-6 --NUMERIC(33, 16)
85-38 51-6 --NUMERIC(34, 16)
...
93-38 55-6 --NUMERIC(38, 16)
*/
检查这些结果：
1。我看到了实际结果比例的算术级数：从22到6，步骤-1。
1.I see an arithmetic progression for the real result scale: from 22 to 6, step -1.
2。此外，如果@ val1和@ val2的比例是常数（ NUMERIC（...，16）），则@ val1&之间存在反相关。 @ val2精度（从16到32）和[实际]结果范围（从16到6）。
2.Also, if the scale for @val1 and @val2 is constant (NUMERIC(...,16)) then an inverse correlation exists between @val1 & @val2 precision (from 16 to 32) and the [real] result scale (from 16 to 6).
3。如果@ val1和@ val2精度为32或更高（ NUMERIC（32-> 38,16）），则[实际]结果标度始终为6 =>这是您的情况。
3.If @val1 and @val2 precision is 32 or higher (NUMERIC(32->38,16)) the the [real] result scale is always 6 => this is your case.
4。如果需要更大的[真实]结果范围（大于6），则需要对@ val1
和@ val2使用较低的精度： NUMERIC（ 22，16）：
4.If a greater [real] result scale is needed (over 6) you need to use a lower precision for @val1
and @val2: NUMERIC(22, 16):
SELECT
CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) [CONVERT(NUMERIC(22,16)]
,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'BaseType') [BaseType]
,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'Precision') [Precision]
,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'Scale') [Scale]
CONVERT(NUMERIC(22,16) BaseType Precision Scale
---------------------- -------- --------- -----
1.3429245542165299 numeric 38 16
这篇关于SQL数值数据类型截断值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
