numeric server sql 有空值加和_sql-server – NUMERIC和DECIMAL之间有什么区别吗？

它们实际上是等价的,但它们是独立的类型,而不是技术上的同义词,如ROWVERSION和TIMESTAMP – 尽管是

they may have been referred to as synonyms in the documentation at one time.这是同义词的略有不同的含义(例如,除了名称之外,它们是无法区分的,而不是另一个的别名) .讽刺吧？

我从MSDN中的措辞中解释的实际上是：

These types are identical,they just have different names.

除了type_id值,这里的所有内容都是相同的：

SELECT * FROM sys.types WHERE name IN (N'numeric',N'decimal');

我完全不知道两者之间的任何行为差异,并且回到sql Server 6.5,始终将它们视为100％可互换.

for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a “conversion”?

只有你明确这样做.您可以通过创建表格然后检查查询计划来查看执行显式或 – 您可能期望的 – 隐式转换的查询,从而轻松证明这一点.这是一个简单的表格：

CREATE TABLE [dbo].[NumDec]

(

[num] [numeric](18,0) NULL,[dec] [decimal](18,0) NULL

);

现在运行这些查询并捕获计划：

DECLARE @num NUMERIC(18,0);

DECLARE @dec DECIMAL(18,0);

SELECT

CONVERT(DECIMAL(18,0),[num]),-- conversion

CONVERT(NUMERIC(18,[dec]) -- conversion

FROM dbo.NumDec

UNION ALL SELECT [num],[dec]

FROM dbo.NumDec WHERE [num] = @dec -- no conversion

UNION ALL SELECT [num],[dec]

FROM dbo.NumDec WHERE [dec] = @num; -- no conversion

但表达式选项卡肯定是：

正如我在上面评论的那样,我们有明确的转换,我们要求它们,但没有明确的转换,我们可能已经预期它们.似乎优化器也将它们视为可互换的.

继续尝试这个测试(数据和索引).

CREATE TABLE [dbo].[NumDec2]

(

[num] [numeric](18,2) NULL,2) NULL

);

INSERT dbo.NumDec2([num],[dec])

SELECT [object_id] + 0.12,[object_id] + 0.12

FROM sys.all_columns;

CREATE INDEX [ix_num] ON dbo.NumDec2([num]);

CREATE INDEX [ix_dec] ON dbo.NumDec2([dec]);

现在运行此查询：

DECLARE @num NUMERIC(18,2) = -1291334356.88,@dec NUMERIC(18,2) = -1291334356.88;

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = @num

UNION ALL

SELECT [num] FROM dbo.NumDec2 WHERE [num] = @dec;

计划没有转换(事实上,“表达式”选项卡为空)：

即使这些也不会导致任何意外的转换.当然,您可以在谓词中的RHS上看到它,但在任何情况下都不会对列数据进行任何转换以促进搜索(更少的扫描力).

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,2),@num)

UNION ALL

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,@dec)

UNION ALL

SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,@num)

UNION ALL

SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,@dec)

UNION ALL

SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,@num)

UNION ALL

SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,@dec)

UNION ALL

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,@num)

UNION ALL

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,@dec);

就个人而言,我更喜欢使用术语DECIMAL,因为它更加准确和具有描述性. BIT也是“数字”.

* Disclaimer: I work for sql Sentry.