SQL Server的小数数值类型(float 和 decimal)用法

  1. 云栖社区>
  2. 博客列表>
  3. 正文

SQL Server的小数数值类型(float 和 decimal)用法

长征6号 2016-03-10 17:01:00 浏览353 评论0

摘要: 在SQL Server中,小数数值实际上只有两种数据类型:float 和 decimal。double precision 整体是数据类型,等价于 float(53),real等价于float(24),应该避免在程序中直接使用 double precision 和 real,而是用 float 代替。

在SQL Server中,小数数值实际上只有两种数据类型:float 和 decimal。double precision 整体是数据类型,等价于 float(53),real等价于float(24),应该避免在程序中直接使用 double precision 和 real,而是用 float 代替。numeric 和 decimal是同义词。

float是近似数值,存在精度缺失,Decimal是精确数值,不存在精度损失。当数值不允许精度丢失时,使用 decimal数据类型存储。在计算小数的除法时,SQL Server 内部隐式升级数据类型,根据小数数值的数据类型,就近向float(24) 或float(53)转换。

一:近似数值,存在精度损失

1,float 表示近似数值

float数据类型的默认类型是float(53),占用8bytes,实际上,float 只能表示两种类型float(53) 和 float(24),分别占用 4Bytes 和 8Bytes。

float [ (n)

Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

 

n value 

Precision 

Storage size

1-24    

7 digits  

4 bytes 

25-53   

15 digits

8 bytes    

Note:SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.

2,在SQL Server中,小数常量的默认数据类型是decimal,decimal的优先级比float高。

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.

示例1,由于在TSQL中,小数数值会自动转换为numeric类型,1.0自动转换为numeric(2, 1),但是在计算除法时,SQL Server 将1.0隐式转换为float(24)。

declare @f_low float(24)
declare @f_high float(53)
select @f_low=cast(1.0 as float(24))/3,@f_high=cast(1.0 as float(53))/3
select 1.0/3 as f,@f_low as f_low, @f_high as f_high

在 WHERE 子句中设置搜索条件(特别是 = 和 <> 运算符),应避免使用 float 列,float 列最好只限于 > 比较或 < 比较。
二,精确数值,不存在精度损失

1,decimal 数据类型需要分别指定小数数值的最大位数(p)和小数位的数量(s),decimal 和 numeric 是等价的。

Numeric data types that have fixed precision and scale.

decimal  [ (p[ ,s] )]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. 

p (precision)             

The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)             

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. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Precision

Storage bytes

1 - 9

5

10-19

9

20-28

13

29-38

17

Note:p 和 s 必须遵守规则:0 <= s <= p <= 38

numeric 和 decimal 数据类型的默认最大精度为 38。在 Transact-SQL 中,numeric 的功能等同于 decimal 数据类型。

decimal 数据类型最多可以存储 38 个数字,所有这些数字均可位于小数点后面。decimal 数据类型存储精确的数字表示形式,存储值没有近似值。

定义 decimal 列、变量和参数的两种属性为:

  • p   指定精度或对象能够支持的数字个数。

  • s   指定可以放在小数点右边的小数位数或数字个数。

  • p 和 s 必须遵守规则:0 <= s <= p <= 38。

numeric 和 decimal 数据类型的默认最大精度为 38。在 Transact-SQL 中,numeric 的功能等同于 decimal 数据类型。

2,示例

declare @dec decimal(38,37)
declare @num decimal(38,37)

select @dec=cast(1.0 as decimal(38,37))/3,@num=cast(1.0 as NUMERIC(38,37))
select @dec,@num,1.0/3,cast(1.0 as float(24))/3,1.000000000000/3,cast(1.0 as float(53))/3

3,默认情况下,SQL Server将小数常量作为decimal 数据类型,在计算小数的除法时,就近进行数据类型的升级,转换为float(24)或float(53) 数据类型。

在 Transact-SQL 语句中,小数数值的常量自动转换为 decimal 数据类型,在转换时,使用最小的精度和小数位数。例如,常量 12.345 被转换为 numeric 值,其精度为 5,小数位为 3。

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.

三,将小数转换成字符串(varchar)

相比cast(float_expression as float(24/53)),使用 str 函数能够有效控制近似数值的小数位数,函数str获取的是近视数值。

STR ( float_expression [ , length [ , decimal ] ] )
  • length是小数的总位数,包含正负符号,小数点,小数点左边和右边数字个数之和;
  • decimal是小数位的数量(小数点右边数字个数),小数位最大为16位,不能超过16,否则,会被截断为16位。如果小数位没有decimal多,那么右边补0。
  • 返回值是varchar类型。

1,对小数常量转换为varchar类型,减少小数位的数量,由2位减少为1位。

SELECT STR(123.45, 6, 1);

2,将decimal 变量转换为varchar类型

declare @d decimal(10,2)
set @d=123.45

SELECT STR(@d, 6, 1);

3,将 float 表达式的值转换为varchar 类型

1.0/3 默认转换为float(24) 类型,因此只有6位小数,小于decimal 参数的8位,右边补两个0。

SELECT STR(1.0/3, 10, 8);

4,在将float和decimal转换为varchar类型时,使用函数str或cast强制转换,返回的数值可能是不相同

复制代码
declare @dt decimal(38,30)
declare @df float(53)

set @dt=50.8863983154297
set @df=50.8863983154297

select @df as df,@dt as dt,
    str(@dt,38,30) as str_dt,str(@df,38,30) as str_df,
    cast(@dt as varchar(100)) as var_dt,cast(@df as varchar(100)) as var_df
复制代码

字段str_dt和str_df的值是不相同的,str函数首先对这两个小数数值取近似值,使用cast强制转换,对于decimal,返回的是精确值,对于float,返回的是近似值。

作者悦光阴
出处http://www.cnblogs.com/ljhdo/
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
分类: SQL Server
标签: 小数, str






本文转自悦光阴博客园博客,原文链接:http://www.cnblogs.com/ljhdo/p/4910699.html,如需转载请自行联系原作者
【云栖快讯】你想见的Java技术专家都在这了,向大佬提问,有问题必答  详情请点击

网友评论

长征6号
文章1375篇 | 关注5
关注
云数据库SQL Server,具有企业许可授权,权限更为开放,引擎功能更为强大。能完美支持W... 查看详情
云数据库PPAS版,是阿里云与EnterpriseDB公司合作基于PostgreSQL高度兼... 查看详情
PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航