Sql-server – ny difference at all between NUMERIC and DECIMAL

sql server

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?

Best Answer

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