SQL Server decimal 与 numeric 区别

SQLServer 2021-04-18

1695

主流关系型数据库都有 decimal 和 numeric 这两个数据类型，但往往都不太区分使用。SQL Server 同样也存在这两个数据类型，这也是过去制定的SQL标准遗留下来的。decimal 和 numeric 在 SQL 标准中可以说是等价的的，在SQL Server 中是一样的，可以查看类型定义确认：

SELECT * FROM sys.types WHERE name IN('DECIMAL','NUMERIC')
SELECT * FROM sys.systypes WHERE name IN('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 标准中对两者的描述：

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]

现在查看测试：

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]) 
ALTER TABLE [TypeTest] ADD CONSTRAINT FK_TypeTest FOREIGN KEY([NumType]) REFERENCES [TypeTest]([DecType])

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

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

sql server

文章转载自SQLServer，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL Server decimal 与 numeric 区别

评论