SQL Server:使用数字文字进行计算 [英] SQL Server: Calculation with numeric literals
问题描述
当我写信时,打印1.0 /(1.0 / 60.0)
结果是 p>
60.0024000960
<当我写相同的公式,并显式转换为 float
print cast(1.0 as float)/(cast(1.0 as float)/ cast(60.0 as float))
结果是
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
到现在为止,我认为带有小数位的数字文字被自动视为 float
值具有适当的精度。投射到 real
显示与转换为 float
相同的结果。
float
获得更好的精度(这听起来像是对我的讽刺:)?SQL Server使用最小的数据类型。
当你运行这个脚本的时候
SELECT SQL_VARIANT_PROPERTY(1.0,碱基类型')
选择SQL_VARIANT_PROPERTY(1.0, '精密')
选择SQL_VARIANT_PROPERTY(1.0, '缩放')
选择SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')
您将看到SQL Server隐式使用了NUMERIC(2,1)数据类型。
除以60.0将结果转换为NUMERIC(8, 6)。
最终的计算结果转换为NUMERIC(17,10)。
$ hr
编辑
取自SQL Server联机丛书数据类型转换
在Transact-SQL语句,一个带有小数点的常量
自动被
转换成一个数值数据值,
使用最小精度和比例尺
。例如,常量
12.345被转换成一个精度为5的数字值和一个
比例为3的数值。
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
float
get better precision (which sounds like irony to me :)? - Is there an easier way than cluttering my formulas with casts?
SQL Server uses the smallest possible datatype.
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).
Edit
Taken from SQL Server Books Online Data Type Conversion
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.
这篇关于SQL Server:使用数字文字进行计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!