Is there any difference between DECIMAL and NUMERIC data types in SQL Server?
When should I use DECIMAL and when NUMERIC?
Add a comment |
6 Answers
They are the same. Numeric is functionally equivalent to decimal.
MSDN: decimal and numeric
8 Comments
vstrien
Functional equivalence is not the same as equality. Actually, in the instructor's slides of the MS6232A course there's a comment added that they are ALMOST the same. At the same time, no recommendation is made by Microsoft regarding one or another (however DECIMAL makes more sense, as it's a standard's datatype instead of a legacy datatype from Sybase). Still wondering what the real difference (behind the screens) is :-).
Guffa
@vstrien: The only difference that I can find is that in the SQL-92 standard
decimal is exactly as precise as declared, while numeric is at least as precise as declared. In SQL Server both are exactly as precise as declared, i.e. it doesn't use the flexibility for numeric that the standard allows.
Doug_Ivison
NOTE, however, that SQL Server does not treat them as interchangeable: if, for example, you have a "parent" column in "DECIMAL(18,0)” format, and you try to add a foreign key for it that references a column in "NUMERIC(18,0)" format, you will get the error
Column '<referencedColumn>' is not the same data type as referencing column '<parentTable>.<parentColumn>' in foreign key '<yourKeyName>'. They have to both be NUMERIC(x,y), or both be DECIMAL(x,y).
Gili
@Guffa: your answer contradicts the SQL2003 standard as quoted at stackoverflow.com/a/759606/14731. Specifically,
decimal is at least as precise as declared, whereas numeric is exactly as precise as declared.Guffa
@Gili: Yes, on review of the original material it seems that you are right that I did swap them around.
|
This is what then SQL2003 standard (§6.1 Data Types) says about the two:
<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>. 4 Comments
gbn
You have link for this please?
Joakim Backman
Wiscorp has an draft version of the standard for download (wiscorp.com/sql_2003_standard.zip), if you want the final version you have to buy it (en.wikipedia.org/wiki/SQL2003#Documentation_availability).
Guffa
Note that this is the SQL standard, not a description of how SQL Server implements it.
one.beat.consumer
Nice to know this is yet another rule Microsoft decided to ignore. :)
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 ;)
1 Comment
Reversed Engineer
Re the edit: Nope - see @JoakimBackman's answer above - he quotes the SQL standard with both NUMERIC and DECIMAL.
Joakim Backman's answer is specific, but this may bring additional clarity to it.
There is a minor difference. As per SQL For Dummies, 8th Edition (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.
Comments
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 
Comments
They are exactly the same. When you use it be consistent. Use one of them in your database
