SQL Server decimal 和 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-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

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

现在直接赋值查询：

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

可以看到，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)

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

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

好了，出现隐式转换了！！字段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]

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

对表创建外键，主外键类型不一样。可确定，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）