Difference between DECIMAL and NUMERIC
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
What's the difference between the SQL datatype NUMERIC
and DECIMAL
?
If databases treat these differently, I'd like to know how for at least:
- SQL Server
- Oracle
- Db/2
- MySQL
- PostgreSQL
Furthermore, are there any differences in how database drivers interpret these types?
sql types decimal numeric
add a comment |
What's the difference between the SQL datatype NUMERIC
and DECIMAL
?
If databases treat these differently, I'd like to know how for at least:
- SQL Server
- Oracle
- Db/2
- MySQL
- PostgreSQL
Furthermore, are there any differences in how database drivers interpret these types?
sql types decimal numeric
1
For Oracle the same because both are converted toNUMBER
download.oracle.com/docs/cd/E11882_01/server.112/e17118/…
– Nobody Tells
Sep 25 '12 at 8:33
add a comment |
What's the difference between the SQL datatype NUMERIC
and DECIMAL
?
If databases treat these differently, I'd like to know how for at least:
- SQL Server
- Oracle
- Db/2
- MySQL
- PostgreSQL
Furthermore, are there any differences in how database drivers interpret these types?
sql types decimal numeric
What's the difference between the SQL datatype NUMERIC
and DECIMAL
?
If databases treat these differently, I'd like to know how for at least:
- SQL Server
- Oracle
- Db/2
- MySQL
- PostgreSQL
Furthermore, are there any differences in how database drivers interpret these types?
sql types decimal numeric
sql types decimal numeric
edited Mar 23 '18 at 6:35
DineshDB
3,97942339
3,97942339
asked Dec 3 '09 at 18:30
leeeroyleeeroy
5,750154550
5,750154550
1
For Oracle the same because both are converted toNUMBER
download.oracle.com/docs/cd/E11882_01/server.112/e17118/…
– Nobody Tells
Sep 25 '12 at 8:33
add a comment |
1
For Oracle the same because both are converted toNUMBER
download.oracle.com/docs/cd/E11882_01/server.112/e17118/…
– Nobody Tells
Sep 25 '12 at 8:33
1
1
For Oracle the same because both are converted to
NUMBER
download.oracle.com/docs/cd/E11882_01/server.112/e17118/…– Nobody Tells
Sep 25 '12 at 8:33
For Oracle the same because both are converted to
NUMBER
download.oracle.com/docs/cd/E11882_01/server.112/e17118/…– Nobody Tells
Sep 25 '12 at 8:33
add a comment |
4 Answers
4
active
oldest
votes
They are the same for almost all purposes.
At one time different vendors used different names (Numeric/Decimal) for almost the same thing. SQL-92 made them the same with one minor difference which can be vendor specific:
NUMERIC must be exactly as precise as it is defined — so if you define 4 decimal places, the DB must always store 4 decimal places.
DECIMAL must be at least as precise as it is defined. This means that the database can actually store more digits than specified (due to the behind-the-scenes storage having space for extra digits). This means the database might store 1.00005
instead of 1.0000
, affecting future calculations.
In SQL Server Numeric is defined as being identical to Decimal in every way — both will always store only the specified number of decimal places.
2
is the decimal/numeric thing not the other way around? stackoverflow.com/questions/759401/…
– gbn
Dec 3 '09 at 19:01
2
Yes, that is correct.
– David
Dec 3 '09 at 19:25
Edited to be "the other way around", for clarity; I think David's strikethroughs were more confusing than helpful.
– Quuxplusone
Dec 2 '13 at 23:18
add a comment |
They are synonyms, no difference at all.
At least on SQL Server in the ANSI SQL standards.
This SO answer shows some difference in ANSI but I suspect in implementation they are the same
1
Same for Oracle: techonthenet.com/oracle/datatypes.php
– OMG Ponies
Dec 3 '09 at 18:56
add a comment |
Postgres: No difference
in documentation description in table 8.1 looks same, yet it is not explained why it is mentioned separately, so
according to Tom Lane post
There isn't any difference, in
Postgres. There are two type names because the SQL standard requires
us to accept both names. In a quick look in the standard it appears
that the only difference is this:
17)NUMERIC specifies the data type exact numeric, with the decimal
precision and scale specified by the <precision> and <scale>.
18)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>.
ie, for DECIMAL the implementation is allowed to allow more digits
than requested to the left of the decimal point. Postgres doesn't
exercise that freedom so there's no difference between these types for
us.
regards, tom lane
also a page lower docs state clearly, that
The types decimal and numeric are equivalent. Both types are part of
the SQL standard.
and also at aliases table decimal [ (p, s) ]
is mentioned as alias for numeric [ (p, s) ]
add a comment |
They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?
What I interpret from the wording in MSDN is actually:
These types are identical, they just have different names.
Other than the type_id values, everything here is identical:
SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');
I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.
for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?
Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:
CREATE TABLE [dbo].[NumDec]
(
[num] [numeric](18, 0) NULL,
[dec] [decimal](18, 0) NULL
);
Now run these queries and capture the plan:
DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);
SELECT
CONVERT(DECIMAL(18,0), [num]), -- conversion
CONVERT(NUMERIC(18,0), [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
we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.
Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f1841915%2fdifference-between-decimal-and-numeric%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
They are the same for almost all purposes.
At one time different vendors used different names (Numeric/Decimal) for almost the same thing. SQL-92 made them the same with one minor difference which can be vendor specific:
NUMERIC must be exactly as precise as it is defined — so if you define 4 decimal places, the DB must always store 4 decimal places.
DECIMAL must be at least as precise as it is defined. This means that the database can actually store more digits than specified (due to the behind-the-scenes storage having space for extra digits). This means the database might store 1.00005
instead of 1.0000
, affecting future calculations.
In SQL Server Numeric is defined as being identical to Decimal in every way — both will always store only the specified number of decimal places.
2
is the decimal/numeric thing not the other way around? stackoverflow.com/questions/759401/…
– gbn
Dec 3 '09 at 19:01
2
Yes, that is correct.
– David
Dec 3 '09 at 19:25
Edited to be "the other way around", for clarity; I think David's strikethroughs were more confusing than helpful.
– Quuxplusone
Dec 2 '13 at 23:18
add a comment |
They are the same for almost all purposes.
At one time different vendors used different names (Numeric/Decimal) for almost the same thing. SQL-92 made them the same with one minor difference which can be vendor specific:
NUMERIC must be exactly as precise as it is defined — so if you define 4 decimal places, the DB must always store 4 decimal places.
DECIMAL must be at least as precise as it is defined. This means that the database can actually store more digits than specified (due to the behind-the-scenes storage having space for extra digits). This means the database might store 1.00005
instead of 1.0000
, affecting future calculations.
In SQL Server Numeric is defined as being identical to Decimal in every way — both will always store only the specified number of decimal places.
2
is the decimal/numeric thing not the other way around? stackoverflow.com/questions/759401/…
– gbn
Dec 3 '09 at 19:01
2
Yes, that is correct.
– David
Dec 3 '09 at 19:25
Edited to be "the other way around", for clarity; I think David's strikethroughs were more confusing than helpful.
– Quuxplusone
Dec 2 '13 at 23:18
add a comment |
They are the same for almost all purposes.
At one time different vendors used different names (Numeric/Decimal) for almost the same thing. SQL-92 made them the same with one minor difference which can be vendor specific:
NUMERIC must be exactly as precise as it is defined — so if you define 4 decimal places, the DB must always store 4 decimal places.
DECIMAL must be at least as precise as it is defined. This means that the database can actually store more digits than specified (due to the behind-the-scenes storage having space for extra digits). This means the database might store 1.00005
instead of 1.0000
, affecting future calculations.
In SQL Server Numeric is defined as being identical to Decimal in every way — both will always store only the specified number of decimal places.
They are the same for almost all purposes.
At one time different vendors used different names (Numeric/Decimal) for almost the same thing. SQL-92 made them the same with one minor difference which can be vendor specific:
NUMERIC must be exactly as precise as it is defined — so if you define 4 decimal places, the DB must always store 4 decimal places.
DECIMAL must be at least as precise as it is defined. This means that the database can actually store more digits than specified (due to the behind-the-scenes storage having space for extra digits). This means the database might store 1.00005
instead of 1.0000
, affecting future calculations.
In SQL Server Numeric is defined as being identical to Decimal in every way — both will always store only the specified number of decimal places.
edited Jan 29 '16 at 16:28
Tom Hubbard
9,867124779
9,867124779
answered Dec 3 '09 at 18:48
DavidDavid
19.7k85479
19.7k85479
2
is the decimal/numeric thing not the other way around? stackoverflow.com/questions/759401/…
– gbn
Dec 3 '09 at 19:01
2
Yes, that is correct.
– David
Dec 3 '09 at 19:25
Edited to be "the other way around", for clarity; I think David's strikethroughs were more confusing than helpful.
– Quuxplusone
Dec 2 '13 at 23:18
add a comment |
2
is the decimal/numeric thing not the other way around? stackoverflow.com/questions/759401/…
– gbn
Dec 3 '09 at 19:01
2
Yes, that is correct.
– David
Dec 3 '09 at 19:25
Edited to be "the other way around", for clarity; I think David's strikethroughs were more confusing than helpful.
– Quuxplusone
Dec 2 '13 at 23:18
2
2
is the decimal/numeric thing not the other way around? stackoverflow.com/questions/759401/…
– gbn
Dec 3 '09 at 19:01
is the decimal/numeric thing not the other way around? stackoverflow.com/questions/759401/…
– gbn
Dec 3 '09 at 19:01
2
2
Yes, that is correct.
– David
Dec 3 '09 at 19:25
Yes, that is correct.
– David
Dec 3 '09 at 19:25
Edited to be "the other way around", for clarity; I think David's strikethroughs were more confusing than helpful.
– Quuxplusone
Dec 2 '13 at 23:18
Edited to be "the other way around", for clarity; I think David's strikethroughs were more confusing than helpful.
– Quuxplusone
Dec 2 '13 at 23:18
add a comment |
They are synonyms, no difference at all.
At least on SQL Server in the ANSI SQL standards.
This SO answer shows some difference in ANSI but I suspect in implementation they are the same
1
Same for Oracle: techonthenet.com/oracle/datatypes.php
– OMG Ponies
Dec 3 '09 at 18:56
add a comment |
They are synonyms, no difference at all.
At least on SQL Server in the ANSI SQL standards.
This SO answer shows some difference in ANSI but I suspect in implementation they are the same
1
Same for Oracle: techonthenet.com/oracle/datatypes.php
– OMG Ponies
Dec 3 '09 at 18:56
add a comment |
They are synonyms, no difference at all.
At least on SQL Server in the ANSI SQL standards.
This SO answer shows some difference in ANSI but I suspect in implementation they are the same
They are synonyms, no difference at all.
At least on SQL Server in the ANSI SQL standards.
This SO answer shows some difference in ANSI but I suspect in implementation they are the same
edited May 23 '17 at 12:02
Community♦
11
11
answered Dec 3 '09 at 18:33
gbngbn
349k59490581
349k59490581
1
Same for Oracle: techonthenet.com/oracle/datatypes.php
– OMG Ponies
Dec 3 '09 at 18:56
add a comment |
1
Same for Oracle: techonthenet.com/oracle/datatypes.php
– OMG Ponies
Dec 3 '09 at 18:56
1
1
Same for Oracle: techonthenet.com/oracle/datatypes.php
– OMG Ponies
Dec 3 '09 at 18:56
Same for Oracle: techonthenet.com/oracle/datatypes.php
– OMG Ponies
Dec 3 '09 at 18:56
add a comment |
Postgres: No difference
in documentation description in table 8.1 looks same, yet it is not explained why it is mentioned separately, so
according to Tom Lane post
There isn't any difference, in
Postgres. There are two type names because the SQL standard requires
us to accept both names. In a quick look in the standard it appears
that the only difference is this:
17)NUMERIC specifies the data type exact numeric, with the decimal
precision and scale specified by the <precision> and <scale>.
18)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>.
ie, for DECIMAL the implementation is allowed to allow more digits
than requested to the left of the decimal point. Postgres doesn't
exercise that freedom so there's no difference between these types for
us.
regards, tom lane
also a page lower docs state clearly, that
The types decimal and numeric are equivalent. Both types are part of
the SQL standard.
and also at aliases table decimal [ (p, s) ]
is mentioned as alias for numeric [ (p, s) ]
add a comment |
Postgres: No difference
in documentation description in table 8.1 looks same, yet it is not explained why it is mentioned separately, so
according to Tom Lane post
There isn't any difference, in
Postgres. There are two type names because the SQL standard requires
us to accept both names. In a quick look in the standard it appears
that the only difference is this:
17)NUMERIC specifies the data type exact numeric, with the decimal
precision and scale specified by the <precision> and <scale>.
18)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>.
ie, for DECIMAL the implementation is allowed to allow more digits
than requested to the left of the decimal point. Postgres doesn't
exercise that freedom so there's no difference between these types for
us.
regards, tom lane
also a page lower docs state clearly, that
The types decimal and numeric are equivalent. Both types are part of
the SQL standard.
and also at aliases table decimal [ (p, s) ]
is mentioned as alias for numeric [ (p, s) ]
add a comment |
Postgres: No difference
in documentation description in table 8.1 looks same, yet it is not explained why it is mentioned separately, so
according to Tom Lane post
There isn't any difference, in
Postgres. There are two type names because the SQL standard requires
us to accept both names. In a quick look in the standard it appears
that the only difference is this:
17)NUMERIC specifies the data type exact numeric, with the decimal
precision and scale specified by the <precision> and <scale>.
18)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>.
ie, for DECIMAL the implementation is allowed to allow more digits
than requested to the left of the decimal point. Postgres doesn't
exercise that freedom so there's no difference between these types for
us.
regards, tom lane
also a page lower docs state clearly, that
The types decimal and numeric are equivalent. Both types are part of
the SQL standard.
and also at aliases table decimal [ (p, s) ]
is mentioned as alias for numeric [ (p, s) ]
Postgres: No difference
in documentation description in table 8.1 looks same, yet it is not explained why it is mentioned separately, so
according to Tom Lane post
There isn't any difference, in
Postgres. There are two type names because the SQL standard requires
us to accept both names. In a quick look in the standard it appears
that the only difference is this:
17)NUMERIC specifies the data type exact numeric, with the decimal
precision and scale specified by the <precision> and <scale>.
18)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>.
ie, for DECIMAL the implementation is allowed to allow more digits
than requested to the left of the decimal point. Postgres doesn't
exercise that freedom so there's no difference between these types for
us.
regards, tom lane
also a page lower docs state clearly, that
The types decimal and numeric are equivalent. Both types are part of
the SQL standard.
and also at aliases table decimal [ (p, s) ]
is mentioned as alias for numeric [ (p, s) ]
edited Nov 8 '16 at 14:59
answered Nov 8 '16 at 14:51
Vao TsunVao Tsun
28.2k63461
28.2k63461
add a comment |
add a comment |
They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?
What I interpret from the wording in MSDN is actually:
These types are identical, they just have different names.
Other than the type_id values, everything here is identical:
SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');
I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.
for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?
Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:
CREATE TABLE [dbo].[NumDec]
(
[num] [numeric](18, 0) NULL,
[dec] [decimal](18, 0) NULL
);
Now run these queries and capture the plan:
DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);
SELECT
CONVERT(DECIMAL(18,0), [num]), -- conversion
CONVERT(NUMERIC(18,0), [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
we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.
Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.
add a comment |
They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?
What I interpret from the wording in MSDN is actually:
These types are identical, they just have different names.
Other than the type_id values, everything here is identical:
SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');
I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.
for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?
Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:
CREATE TABLE [dbo].[NumDec]
(
[num] [numeric](18, 0) NULL,
[dec] [decimal](18, 0) NULL
);
Now run these queries and capture the plan:
DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);
SELECT
CONVERT(DECIMAL(18,0), [num]), -- conversion
CONVERT(NUMERIC(18,0), [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
we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.
Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.
add a comment |
They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?
What I interpret from the wording in MSDN is actually:
These types are identical, they just have different names.
Other than the type_id values, everything here is identical:
SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');
I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.
for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?
Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:
CREATE TABLE [dbo].[NumDec]
(
[num] [numeric](18, 0) NULL,
[dec] [decimal](18, 0) NULL
);
Now run these queries and capture the plan:
DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);
SELECT
CONVERT(DECIMAL(18,0), [num]), -- conversion
CONVERT(NUMERIC(18,0), [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
we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.
Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.
They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?
What I interpret from the wording in MSDN is actually:
These types are identical, they just have different names.
Other than the type_id values, everything here is identical:
SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');
I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.
for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?
Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:
CREATE TABLE [dbo].[NumDec]
(
[num] [numeric](18, 0) NULL,
[dec] [decimal](18, 0) NULL
);
Now run these queries and capture the plan:
DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);
SELECT
CONVERT(DECIMAL(18,0), [num]), -- conversion
CONVERT(NUMERIC(18,0), [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
we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.
Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.
answered Nov 17 '18 at 7:01
Coder GirlCoder Girl
527
527
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f1841915%2fdifference-between-decimal-and-numeric%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
For Oracle the same because both are converted to
NUMBER
download.oracle.com/docs/cd/E11882_01/server.112/e17118/…– Nobody Tells
Sep 25 '12 at 8:33