47

I know that the NUMERIC and DECIMAL data types in SQL Server work the same: the syntax for creating them is the same, the ranges of values you can store in them is the same, etc.

However, the MSDN documentation describes the relationship between the two as this:

numeric is functionally equivalent to decimal.

Normally, when I see the the qualifier "functionally equivalent", it means that the two things aren't exactly the same , but that they are two different types that are indistinguishable from the outside.

Is this implication true? Are there differences between NUMERIC and DECIMAL that just happen to behave the same to an outside observer? Or are they actually equivalent, e.g. is NUMERIC just a legacy synonym for DECIMAL?

56

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

As shown in SQL Sentry Plan Explorer*, the plan is not really interesting:

enter image description here

But the Expressions tab sure is:

enter image description here

As I commented above, 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.

Go ahead and try this test, too (data and indexes).

CREATE TABLE [dbo].[NumDec2]
(
    [num] [numeric](18, 2) NULL,
    [dec] [decimal](18, 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]);

Now run this query:

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;

Plan has no conversions (in fact the Expressions tab is empty):

enter image description here

Even these don't lead to any unexpected conversions. Of course you see it on the RHS in the predicate, but in no case did any conversion have to occur against the column data to facilitate the seek (much less force a scan).

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

Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.

* Disclaimer: I work for SQL Sentry.
  • I know that SQL treats, e.g. DECIMAL(18,2) and DECIMAL(18,0) as "different types"; does this mean the same true for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"? – KutuluMike Aug 29 '14 at 18:27
  • @MichaelEdenfield Updated my answer for your new question. – Aaron Bertrand Aug 29 '14 at 18:56
  • 1
    I know this is an old answer but I just found a case where thinking that numeric and decimal are synonyms caused SQL Server to raise an error. Just thought I should leave a comment here in case someone else might benefit from it. – Zohar Peled Mar 9 '18 at 10:31
  • @AaronBertrand I voted up for your answer because it is helpful. Both types look to be identical so if that is the case, why are they named differently? is there any historical reason? – Ivanzinho May 17 at 15:47
  • @Ivanzinho I'm not sure if your question is rhetorical, there are a bunch of theories (both on this page and the answer you linked), but if you want a definitive answer, you are probably going to be out of luck. You'd need to track down the original engineers who first implemented both types in SQL Server, and rely on their memory of both that implementation and their interpretation of the standard at that time. – Aaron Bertrand May 17 at 19:10
15

They are identical in practice, however (from SQL 2003 standard):

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.

  • 5
    The fundamental question then (and I believe the answer is no), did SQL Server implement DECIMAL's precision differently than NUMERIC's. What the standard says could be done is far less relevant than what was actually done. – Aaron Bertrand Aug 29 '14 at 18:50
  • 5
    Thanks. This is an excellent answer to the follow-up question of why there are two types with different names but identical behavior. – Gabe Aug 30 '14 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.