I did some testing with floating point calculations to minimize the precision loss. I stumbled across a phenomen I want to show here and hopefully get an explanation.
我做了一些浮点计算测试,以最大限度地减少精度损失。我偶然发现了一个我要在这里展示的现象,并希望得到一个解释。
When I write
当我写作
print 1.0 / (1.0 / 60.0)
the result is
结果是
60.0024000960
When I write the same formula and do explicit casting to float
当我写相同的公式并进行显式转换浮动时
print cast(1.0 as float) / (cast(1.0 as float) / cast(60.0 as float))
the result is
结果是
60
Until now I thought that numeric literals with decimal places are automatically treated as float values with the appropriate precision. Casting to real shows the same result as casting to float.
到目前为止,我认为带小数位的数字文字会自动被视为具有适当精度的浮点值。转换为实际显示与转换为浮动相同的结果。
- Is there some documentation on how SQL Server evaluates numeric literals?
- Of what datatype are those literals?
- Do I really have to cast them to
floatget better precision (which sounds like irony to me :)? - Is there an easier way than cluttering my formulas with casts?
是否有一些关于SQL Server如何评估数字文字的文档?
这些文字的数据类型是什么?
我是否真的必须将它们抛向浮动才能获得更好的精度(这对我来说听起来很讽刺:)?
有没有比使用演员表混乱我的公式更简单的方法?
4 个解决方案
#1
SQL Server uses the smallest possible datatype.
SQL Server使用尽可能小的数据类型。
When you run this script
当您运行此脚本时
SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')
you'll see that SQL Server implicitly used a NUMERIC(2, 1) datatype.
The division by 60.0 converts the result to NUMERIC(8, 6).
The final calculation converts the result to NUMERIC(17, 10).
您将看到SQL Server隐式使用NUMERIC(2,1)数据类型。除以60.0将结果转换为NUMERIC(8,6)。最终计算将结果转换为NUMERIC(17,10)。
Edit
Taken from SQL Server Books Online Data Type Conversion
取自SQL Server联机丛书数据类型转换
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
在Transact-SQL语句中,具有小数点的常量将使用所需的最小精度和小数位自动转换为数字数据值。例如,常量12.345将转换为精度为5且比例为3的数值。
#2
Yes, you frequently have to cast them to float get better precision. My take on it:
是的,你经常需要将它们抛向浮动才能获得更好的精度。我接受它:
For better precision cast decimals before calculations
在计算之前获得更好的精确投射小数
#3
I think it should be understood what is going on behind the scenes for future reference in similar cases.
我认为应该理解幕后发生的事情,以便在类似情况下进一步参考。
Literal numerical values with decimal point excluding scientific notation represent Decimal data type which is stored as smallest possible Decimal type. Same quote as Lieven Keersmaekers's from: https://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal
具有小数点的文字数值(不包括科学记数法)表示十进制数据类型,其存储为最小可能的十进制类型。与Lieven Keersmaekers的相同的引用来自:https://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
在Transact-SQL语句中,具有小数点的常量将使用所需的最小精度和小数位自动转换为数字数据值。例如,常量12.345将转换为精度为5且比例为3的数值。
The trailing zeros on the right of decimal point specify scale. The leading zeros left of decimal point are ignored.
小数点右侧的尾随零指定比例。小数点左边的前导零被忽略。
Some examples:
1.0 -> Decimal(2,1)
60.0 -> Decimal(3,1)
1.00 -> Decimal(3,2)
01.0 -> Decimal (2,1)
Another point to consider is Data Type precedence. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. And yet another point to consider is if we do arithmetic operations on Decimal types that the resulting Decimal type, i.e. precision and scale depend on both operands and operation itself. This is described in document Precision, Scale, and Length.
另一个需要考虑的问题是数据类型优先级。当运算符组合了两种不同数据类型的表达式时,数据类型优先级的规则指定具有较低优先级的数据类型将转换为具有较高优先级的数据类型。还有一点需要考虑的是,如果我们对Decimal类型进行算术运算,那么得到的Decimal类型,即精度和比例都取决于操作数和操作本身。这在文档精度,比例和长度中有所描述。
So, part of your expression in parenthesis
所以,你的表达式的一部分在括号中
( 1.0 / 60.0 ) is evaluated to 0.016666 and the resulting type is Decimal (8,6)
using above rules about Precision and scale of Decimal expressions. In addition the banker's rounding or rounding to even is used. It is important to note different rounding for Decimal and float type are used. If we continue the expression
使用上述有关十进制表达式的精度和比例的规则。此外,使用银行家的舍入或舍入为偶数。重要的是要注意使用Decimal和float类型的不同舍入。如果我们继续表达
1.0 / 0.016666 is evaluated to 60.002400096 and the resulting type is Decimal (17,10)
So the part of the discrepancy is due to different rounding being used for decimal types than for float.
因此,差异的部分是由于十进制类型使用的不同舍入而不是浮点数。
In accordance to the above rules it would be sufficient to use just one cast inside parenthesis. Every other literal will be promoted to float in accordance with Data Type Precedence rules.
根据上述规则,在括号内仅使用一次铸造就足够了。每个其他文字都将根据数据类型优先级规则提升为浮动。
1.0 / (1.0 / cast(60.0 as float))
And one more IMPORTANT thing. Even this float expression does not calculate exact result. It is just so that the front end (SSMS or whatever) rounds the value to (I guess) precision 6 digits and then truncates trailing zeros. So i.e. 1.000001 becomes 1.
还有一个重要的事情。即使这个浮点表达式也不能计算出精确的结果只是这样前端(SSMS或其他)将值舍入(我猜)精度为6位,然后截断尾随零。因此,即1.000001变为1。
Simple, isn't it?
简单,不是吗?
#4
To write a constant float expression, try to use scientific notation:
要编写常量float表达式,请尝试使用科学记数法:
select (1.0E0 / (1.0E0 / 60.0E0))
选择(1.0E0 /(1.0E0 / 60.0E0))
The result is 60.
结果是60。
#1
SQL Server uses the smallest possible datatype.
SQL Server使用尽可能小的数据类型。
When you run this script
当您运行此脚本时
SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')
you'll see that SQL Server implicitly used a NUMERIC(2, 1) datatype.
The division by 60.0 converts the result to NUMERIC(8, 6).
The final calculation converts the result to NUMERIC(17, 10).
您将看到SQL Server隐式使用NUMERIC(2,1)数据类型。除以60.0将结果转换为NUMERIC(8,6)。最终计算将结果转换为NUMERIC(17,10)。
Edit
Taken from SQL Server Books Online Data Type Conversion
取自SQL Server联机丛书数据类型转换
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
在Transact-SQL语句中,具有小数点的常量将使用所需的最小精度和小数位自动转换为数字数据值。例如,常量12.345将转换为精度为5且比例为3的数值。
#2
Yes, you frequently have to cast them to float get better precision. My take on it:
是的,你经常需要将它们抛向浮动才能获得更好的精度。我接受它:
For better precision cast decimals before calculations
在计算之前获得更好的精确投射小数
#3
I think it should be understood what is going on behind the scenes for future reference in similar cases.
我认为应该理解幕后发生的事情,以便在类似情况下进一步参考。
Literal numerical values with decimal point excluding scientific notation represent Decimal data type which is stored as smallest possible Decimal type. Same quote as Lieven Keersmaekers's from: https://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal
具有小数点的文字数值(不包括科学记数法)表示十进制数据类型,其存储为最小可能的十进制类型。与Lieven Keersmaekers的相同的引用来自:https://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
在Transact-SQL语句中,具有小数点的常量将使用所需的最小精度和小数位自动转换为数字数据值。例如,常量12.345将转换为精度为5且比例为3的数值。
The trailing zeros on the right of decimal point specify scale. The leading zeros left of decimal point are ignored.
小数点右侧的尾随零指定比例。小数点左边的前导零被忽略。
Some examples:
1.0 -> Decimal(2,1)
60.0 -> Decimal(3,1)
1.00 -> Decimal(3,2)
01.0 -> Decimal (2,1)
Another point to consider is Data Type precedence. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. And yet another point to consider is if we do arithmetic operations on Decimal types that the resulting Decimal type, i.e. precision and scale depend on both operands and operation itself. This is described in document Precision, Scale, and Length.
另一个需要考虑的问题是数据类型优先级。当运算符组合了两种不同数据类型的表达式时,数据类型优先级的规则指定具有较低优先级的数据类型将转换为具有较高优先级的数据类型。还有一点需要考虑的是,如果我们对Decimal类型进行算术运算,那么得到的Decimal类型,即精度和比例都取决于操作数和操作本身。这在文档精度,比例和长度中有所描述。
So, part of your expression in parenthesis
所以,你的表达式的一部分在括号中
( 1.0 / 60.0 ) is evaluated to 0.016666 and the resulting type is Decimal (8,6)
using above rules about Precision and scale of Decimal expressions. In addition the banker's rounding or rounding to even is used. It is important to note different rounding for Decimal and float type are used. If we continue the expression
使用上述有关十进制表达式的精度和比例的规则。此外,使用银行家的舍入或舍入为偶数。重要的是要注意使用Decimal和float类型的不同舍入。如果我们继续表达
1.0 / 0.016666 is evaluated to 60.002400096 and the resulting type is Decimal (17,10)
So the part of the discrepancy is due to different rounding being used for decimal types than for float.
因此,差异的部分是由于十进制类型使用的不同舍入而不是浮点数。
In accordance to the above rules it would be sufficient to use just one cast inside parenthesis. Every other literal will be promoted to float in accordance with Data Type Precedence rules.
根据上述规则,在括号内仅使用一次铸造就足够了。每个其他文字都将根据数据类型优先级规则提升为浮动。
1.0 / (1.0 / cast(60.0 as float))
And one more IMPORTANT thing. Even this float expression does not calculate exact result. It is just so that the front end (SSMS or whatever) rounds the value to (I guess) precision 6 digits and then truncates trailing zeros. So i.e. 1.000001 becomes 1.
还有一个重要的事情。即使这个浮点表达式也不能计算出精确的结果只是这样前端(SSMS或其他)将值舍入(我猜)精度为6位,然后截断尾随零。因此,即1.000001变为1。
Simple, isn't it?
简单,不是吗?
#4
To write a constant float expression, try to use scientific notation:
要编写常量float表达式,请尝试使用科学记数法:
select (1.0E0 / (1.0E0 / 60.0E0))
选择(1.0E0 /(1.0E0 / 60.0E0))
The result is 60.
结果是60。