在SQL Server中,DECIMAL和NUMERIC有什么区别吗?

时间:2020-12-05 16:56:05

Is there any difference between DECIMAL and NUMERIC data types in SQL Server?

在SQL Server中,小数和数字数据类型有什么区别吗?

When should I use DECIMAL and when NUMERIC?

什么时候我应该使用十进制和数字?

5 个解决方案

#1


96  

They are the same. Numeric is functionally equivalent to decimal.

他们是相同的。数值在功能上等同于小数。

MSDN: decimal and numeric

MSDN:十进制数字

#2


37  

This is what then SQL2003 standard (§6.1 Data Types) says about the two:

这就是SQL2003标准(6.1数据类型)的含义:

 <exact numeric type> ::=
    NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DECIMAL [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | SMALLINT
  | INTEGER
  | INT
  | BIGINT

 ...

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>.

#3


10  

To my knowledge there is no difference between NUMERIC and DECIMAL data types. They are synonymous to each other and either one can be used. DECIMAL and NUMERIC data types are numeric data types with fixed precision and scale.

据我所知,数字和小数数据类型没有区别。它们是彼此的同义词,任何一个都可以使用。十进制和数字数据类型是具有固定精度和规模的数字数据类型。

Edit:

编辑:

Speaking to a few collegues maybe its has something to do with DECIMAL being the ANSI SQL standard and NUMERIC being one Mircosoft prefers as its more commonly found in programming languages. ...Maybe ;)

对于一些同事来说,它可能与小数有关,它是ANSI SQL标准,而数字是一种Mircosoft,它更常用的是编程语言。也许,)

#4


0  

They are synonyms, no difference at all.Decimal and Numeric data types are numeric data types with fixed precision and scale.

它们是同义词,没有区别。十进制和数字数据类型是具有固定精度和规模的数字数据类型。

-- Initialize a variable, give it a data type and an initial value

declare @myvar as decimal(18,8) or numeric(18,8)----- 9 bytes needed

-- Increse that the vaue by 1

set @myvar = 123456.7

--Retrieve that value

select @myvar as myVariable

#5


0  

Joakim Backman's answer is specific, but this may bring additional clarity to it.

Joakim Backman的答案是明确的,但这可能会给它带来额外的清晰度。

There is a minor difference. As per SQL For Dummies, 8th Edition (2013):

有一个小的区别。《傻瓜的SQL》第8版(2013年):

The DECIMAL data type is similar to NUMERIC. ... The difference is that your implementation may specify a precision greater than what you specify — if so, the implementation uses the greater precision. If you do not specify precision or scale, the implementation uses default values, as it does with the NUMERIC type.

小数数据类型类似于数字。区别在于,您的实现可能指定了比您指定的精度更高的精度——如果是这样,那么实现将使用更大的精度。如果不指定精度或规模,则实现使用默认值,就像使用数值类型一样。

It seems that the difference on some implementations of SQL is in data integrity. DECIMAL allows overflow from what is defined based on some system defaults, where as NUMERIC does not.

似乎SQL的某些实现上的差异在于数据完整性。十进制允许在某些系统默认值的基础上溢出,而数值则不能。

#1


96  

They are the same. Numeric is functionally equivalent to decimal.

他们是相同的。数值在功能上等同于小数。

MSDN: decimal and numeric

MSDN:十进制数字

#2


37  

This is what then SQL2003 standard (§6.1 Data Types) says about the two:

这就是SQL2003标准(6.1数据类型)的含义:

 <exact numeric type> ::=
    NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DECIMAL [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | SMALLINT
  | INTEGER
  | INT
  | BIGINT

 ...

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>.

#3


10  

To my knowledge there is no difference between NUMERIC and DECIMAL data types. They are synonymous to each other and either one can be used. DECIMAL and NUMERIC data types are numeric data types with fixed precision and scale.

据我所知,数字和小数数据类型没有区别。它们是彼此的同义词,任何一个都可以使用。十进制和数字数据类型是具有固定精度和规模的数字数据类型。

Edit:

编辑:

Speaking to a few collegues maybe its has something to do with DECIMAL being the ANSI SQL standard and NUMERIC being one Mircosoft prefers as its more commonly found in programming languages. ...Maybe ;)

对于一些同事来说,它可能与小数有关,它是ANSI SQL标准,而数字是一种Mircosoft,它更常用的是编程语言。也许,)

#4


0  

They are synonyms, no difference at all.Decimal and Numeric data types are numeric data types with fixed precision and scale.

它们是同义词,没有区别。十进制和数字数据类型是具有固定精度和规模的数字数据类型。

-- Initialize a variable, give it a data type and an initial value

declare @myvar as decimal(18,8) or numeric(18,8)----- 9 bytes needed

-- Increse that the vaue by 1

set @myvar = 123456.7

--Retrieve that value

select @myvar as myVariable

#5


0  

Joakim Backman's answer is specific, but this may bring additional clarity to it.

Joakim Backman的答案是明确的,但这可能会给它带来额外的清晰度。

There is a minor difference. As per SQL For Dummies, 8th Edition (2013):

有一个小的区别。《傻瓜的SQL》第8版(2013年):

The DECIMAL data type is similar to NUMERIC. ... The difference is that your implementation may specify a precision greater than what you specify — if so, the implementation uses the greater precision. If you do not specify precision or scale, the implementation uses default values, as it does with the NUMERIC type.

小数数据类型类似于数字。区别在于,您的实现可能指定了比您指定的精度更高的精度——如果是这样,那么实现将使用更大的精度。如果不指定精度或规模,则实现使用默认值,就像使用数值类型一样。

It seems that the difference on some implementations of SQL is in data integrity. DECIMAL allows overflow from what is defined based on some system defaults, where as NUMERIC does not.

似乎SQL的某些实现上的差异在于数据完整性。十进制允许在某些系统默认值的基础上溢出,而数值则不能。