SQL Server decimal 和 numeric 区别[亲测有效]

SQL Server decimal 和 numeric 区别[亲测有效]SQLServerdecimal和numeric区别

大家好,欢迎来到IT知识分享网。

最近看到了decimal 和 numeric ,又记不起来区别是什么,还是总结一下。

decimal 和 numeric 在 SQL 标准中可以说是等价的的,在SQL Server 中是一样的(参考:decimal 和 numeric (Transact-SQL)),可以查看类型定义确认:

SELECT * FROM sys.types WHERE name IN('DECIMAL','NUMERIC')
SELECT * FROM sys.systypes WHERE name IN('DECIMAL','NUMERIC')

IT知识分享网

SQL Server decimal 和 numeric 区别[亲测有效]

到底有什么不一样呢?有人在 SQL-92 标准中找出了微妙的描述。

in the SQL-92 standard, decimalisexactly as precise as declared, whilenumericisat least as precise as declared. In SQL Server both are exactly as precise as declared

decimal 完全和声明的一样精确;numeric  至少和声明的一样精确。在 sql server 中两者使用完全和声明的一样精确。但是没有例子,很难明白。事实上,不仅sql server 有这两个类型,其他满足 sql 标准的数据库都有。

SQL2003 标准中对两者的描述:

IT知识分享网21) NUMERIC specifies the data type
    exact numeric, with the decimal
    precision and scale specified by the
    <precision> and <scale>.

22) DECIMAL specifies the data type
    exact numeric, with the decimal scale
    specified by the <scale> and the
    implementation-defined decimal
    precision equal to or greater than the
    value of the specified <precision>.

这段描述比较清楚了,小数类型的定义格式为(P,S),即固定精度和小数位数;numeric  要求固定精度和小数位一样精确,小数位固定长度;而 decimal 不一定,可能会大于或等于固定精度P的长度,即小数位的长度在存储的时候比实际的长,只是我们看不到罢了。。

没有例子,描述确实看不懂。上面是 SQL 标准的描述,在 SQL Server 当然也不一样。

但不管怎样,在 SQL Server 中,两者类型(名称)不同,但可以说是一样的,用哪个都行!

示例测试:

--	DROP TABLE [dbo].[TypeTest]
CREATE TABLE [dbo].[TypeTest](
	[DecType] [decimal](18, 8) NOT NULL,
	[NumType] [numeric](18, 8) NOT NULL
) ON [PRIMARY]
GO


现在查看测试:

IT知识分享网DECLARE @DecType DECIMAL(18,8)
SET @DecType = 3.1415926
SELECT * FROM TypeTest WHERE DecType=@DecType
SELECT * FROM TypeTest WHERE NumType=@DecType
GO
DECLARE @NumType NUMERIC(18,8)
SET @NumType = 3.1415926
SELECT * FROM TypeTest WHERE DecType=@NumType
SELECT * FROM TypeTest WHERE NumType=@NumType
GO

SQL Server decimal 和 numeric 区别[亲测有效]

可以看到,两者传递的类型虽然不一样,但是并没有在内部进行转换,此时传递的参数将 decimal 和 numeric 当做相同的。

现在直接赋值查询:

SELECT * FROM TypeTest WHERE DecType=3.1415926
SELECT * FROM TypeTest WHERE NumType=3.1415926

SQL Server decimal 和 numeric 区别[亲测有效]

可以看到,decimal 类型的没有隐式转换,而 numeric  则进行了类型转换。也就是说传递的 “3.1415926” 既不不是 decimal 类型,也不是 numeric  类型?因为以下的查询是没有隐式转换的。

SELECT * FROM TypeTest WHERE DecType=CONVERT(DECIMAL(20,10),3.1415926)
SELECT * FROM TypeTest WHERE DecType=CONVERT(NUMERIC(20,10),3.1415926)
SELECT * FROM TypeTest WHERE NumType=CONVERT(DECIMAL(20,10),3.1415926)
SELECT * FROM TypeTest WHERE NumType=CONVERT(NUMERIC(20,10),3.1415926)

“3.1415926”  到底是什么类型??

查看该数值类型:

SELECT SQL_VARIANT_PROPERTY(3.1415926,'BaseType') AS [BaseType]
,SQL_VARIANT_PROPERTY(3.1415926,'Precision') AS [Precision]
,SQL_VARIANT_PROPERTY(3.1415926,'Scale') AS [Scale]

——————————————–|

BaseType      Precision   Scale    |

———-          ————-  ———–|
numeric         8                7           |

——————————————  |

可以看到,“3.1415926”  类型确实为 numeric,怎么一个需要隐式转换,一个不需要呢??当显如下sql式转换后查询,发现确实有隐式转换!

SELECT * FROM TypeTest WHERE DecType=CONVERT(NUMERIC(8,7),3.1415926)
SELECT * FROM TypeTest WHERE NumType=CONVERT(NUMERIC(8,7),3.1415926)

【渐渐地,越来越接近真相了…………】

现在转换成与精度一样的类型:

SELECT * FROM TypeTest WHERE DecType=CONVERT(NUMERIC(18,7),3.1415926)
SELECT * FROM TypeTest WHERE NumType=CONVERT(NUMERIC(18,7),3.1415926)

SQL Server decimal 和 numeric 区别[亲测有效]

当固定精度为 18 时,没有发现需要隐式转换,即使小数位长度不一样。现在换成固定精度与字段定义的不一样看看。

SELECT * FROM TypeTest WHERE DecType=CONVERT(NUMERIC(17,7),3.1415926)
SELECT * FROM TypeTest WHERE NumType=CONVERT(NUMERIC(17,7),3.1415926)

SQL Server decimal 和 numeric 区别[亲测有效]

好了,出现隐式转换了!!字段DecType类型(decimal )还是一样没什么影响,而 字段 NumTypel类型(numeric)出现了隐式转换!所以确定,类型 numeric 要求的固定精度大于等于声明的精度!

=====================================

接下来任意测试:

=====================================

故意设置较短的精度,可以看到数值 “321.1415926” 类型为 numeric。即传递的带小数位的数值,默认类型为numeric。

DECLARE @DecType DECIMAL(5,3)
SET @DecType = 321.1415926
GO
DECLARE @NumType NUMERIC(5,3)
SET @NumType = 321.1415926
GO

消息 8115,级别 16,状态 8,第 21 行
将 numeric 转换为数据类型 numeric 时出现算术溢出错误。
消息 8115,级别 16,状态 8,第 24 行
将 numeric 转换为数据类型 numeric 时出现算术溢出错误。

=====================================

将数据类型 decimal 和 numeric 都加上一个小数(如:3.14),发现 decimal  加上任意小时后,类型转变为 numeric 。

DECLARE @DecType DECIMAL(18,8)
DECLARE @NumType NUMERIC(18,8)
SET @DecType = 3.1415926
SET @NumType = 3.1415926

SELECT SQL_VARIANT_PROPERTY(@DecType+3.14,'BaseType') AS [BaseType]
	,SQL_VARIANT_PROPERTY(@DecType+3.14,'Precision') AS [Precision]
	,SQL_VARIANT_PROPERTY(@DecType+3.14,'Scale') AS [Scale]

SELECT SQL_VARIANT_PROPERTY(@NumType+3.14,'BaseType') AS [BaseType]
	,SQL_VARIANT_PROPERTY(@NumType+3.14,'Precision') AS [Precision]
	,SQL_VARIANT_PROPERTY(@NumType+3.14,'Scale') AS [Scale]

SQL Server decimal 和 numeric 区别[亲测有效]

=====================================

对表创建外键,主外键类型不一样。可确定,decimal 和 numeric 类型是不一样的(本来就不一样,名字都不一样,哈哈!)

ALTER TABLE [TypeTest] ADD CONSTRAINT PK_TypeTest PRIMARY KEY([DecType]) 
GO
ALTER TABLE [TypeTest] ADD CONSTRAINT FK_TypeTest FOREIGN KEY([NumType]) REFERENCES [TypeTest]([DecType]) 
GO

消息 1778,级别 16,状态 0,第 13 行
列 ‘TypeTest.DecType’ 的数据类型与外键 ‘FK_TypeTest’ 中的引用列 ‘TypeTest.NumType’ 的数据类型不同。
消息 1750,级别 16,状态 0,第 13 行
无法创建约束或索引。请参阅前面的错误。


最后总结:

数据类型 decimal 和 numeric 虽然在小数存储方面都一样,但建议使用Decimal(原因参考文章红色标注)

相关探讨:

SQLServer 唯一键约束和唯一索引有什么区别?

SQLServer 可变字符怎么设置长度?(如varchar)

免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/12472.html

(0)

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

关注微信