May 5, 2015 at 6:20 pm
Ah, didn't catch that. Thanks!
May 5, 2015 at 10:18 pm
Stephanie Giovannini (5/5/2015)
While decimal and numeric are functionally equivalent, they are not the same type. There are six types in the question.The system_type_id for decimal and numeric is not the same. The other type synonyms (such as REAL and FLOAT(10)) result in the same system_type_id, max_length, precision and scale.
CREATE TABLE #test (dec_12 DECIMAL (12,0),
num_12 NUMERIC (12,0),
int1 INTEGER,
int2 INT,
real0 REAL,
float_10 FLOAT(10),
float0 FLOAT,
double_precision DOUBLE PRECISION,
num_5_4 NUMERIC(5,4));
SELECT name, system_type_id, max_length, precision, scale
FROM tempdb.sys.columns
WHERE OBJECT_ID = OBJECT_ID('tempdb..#test');
SELECT COUNT(*) correct_answer_to_question
FROM (
SELECT DISTINCT system_type_id, max_length, precision, scale
FROM tempdb.sys.columns
WHERE OBJECT_ID = OBJECT_ID('tempdb..#test')) a;
If you create this table (but not in tempdb) and script the table creation, the output looks like this:
CREATE TABLE [dbo].[test]([dec_12] [decimal](12, 0) NULL,
[num_12] [numeric](12, 0) NULL,
[int1] [int] NULL,
[int2] [int] NULL,
[real0] [real] NULL,
[float_10] [real] NULL,
[float0] [float] NULL,
[double_precision] [float] NULL,
[num_5_4] [numeric](5, 4) NULL
)
Yay, some controversy. Seriously, thanks for raising this. To my knowledge the assertion that a NUMERIC and a DECIMAL with the same precision and scale are the same data type holds true within the query optimizer and the storage engine which is where I am coming from. I concede there is some nuance built-in around the system metadata (and, I will add, around keys and type precedence) but in all I have found no evidence of a semantic difference.
The ANSI SQL standard defines NUMERIC and DECIMAL separately and there is a distinction in how each might behave different from one another however that is not the case in SQL Server's implementation of the standard. It would be interesting to hear from someone with knowledge of the design history, the actual SQL Server codebase, or who is privy to more of the internals to hear the story behind why the system types are different and why a pure synonym like INT for INTEGER was not implemented.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 6, 2015 at 1:00 am
Orlando Colamatteo (5/5/2015)
The ANSI SQL standard defines NUMERIC and DECIMAL separately and there is a distinction in how each might behave different from one another however that is not the case in SQL Server's implementation of the standard. It would be interesting to hear from someone with knowledge of the design history, the actual SQL Server codebase, or who is privy to more of the internals to hear the story behind why the system types are different and why a pure synonym like INT for INTEGER was not implemented.
I can't speak as someone with such knowledge, but if I were writing software that had to deal with a standard where NUMERIC and DECIMAL are defined separately, I'd be sure that it was possible to tell those two types apart internally. After all, what if some future version of the standard introduces a substantive difference between the two? If I've defined them to be the same internally, it's going to be a lot of work and break a lot of existing applications to split them out.
May 6, 2015 at 4:01 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 6, 2015 at 6:15 am
G ood question. It caused me to really think it through and caused a twist or two along the way. Thanks.
May 6, 2015 at 7:34 am
Orlando Colamatteo (5/5/2015)
The ANSI SQL standard defines NUMERIC and DECIMAL separately and there is a distinction in how each might behave different from one another however that is not the case in SQL Server's implementation of the standard. It would be interesting to hear from someone with knowledge of the design history, the actual SQL Server codebase, or who is privy to more of the internals to hear the story behind why the system types are different and why a pure synonym like INT for INTEGER was not implemented.
I don't have that information, but I think the answer to the QOTD depends on how you define "same type." It's clear that the type-name synonyms (INT and INTEGER) lose their distinctiveness as soon as the table is created. SQL stores nothing to indicate which type-name-synonym was originally specified. NUMERIC and DECIMAL have the same type implementation, but are not actually the same type because SQL stores them distinctively and is able to reproduce the type names when scripting the table.
May 6, 2015 at 7:36 am
Stephanie Giovannini (5/5/2015)
While decimal and numeric are functionally equivalent, they are not the same type. There are six types in the question.The system_type_id for decimal and numeric is not the same. The other type synonyms (such as REAL and FLOAT(10)) result in the same system_type_id, max_length, precision and scale.
CREATE TABLE #test (dec_12 DECIMAL (12,0),
num_12 NUMERIC (12,0),
int1 INTEGER,
int2 INT,
real0 REAL,
float_10 FLOAT(10),
float0 FLOAT,
double_precision DOUBLE PRECISION,
num_5_4 NUMERIC(5,4));
SELECT name, system_type_id, max_length, precision, scale
FROM tempdb.sys.columns
WHERE OBJECT_ID = OBJECT_ID('tempdb..#test');
SELECT COUNT(*) correct_answer_to_question
FROM (
SELECT DISTINCT system_type_id, max_length, precision, scale
FROM tempdb.sys.columns
WHERE OBJECT_ID = OBJECT_ID('tempdb..#test')) a;
If you create this table (but not in tempdb) and script the table creation, the output looks like this:
CREATE TABLE [dbo].[test]([dec_12] [decimal](12, 0) NULL,
[num_12] [numeric](12, 0) NULL,
[int1] [int] NULL,
[int2] [int] NULL,
[real0] [real] NULL,
[float_10] [real] NULL,
[float0] [float] NULL,
[double_precision] [float] NULL,
[num_5_4] [numeric](5, 4) NULL
)
But the distinct system typ id for numeric data type is 108
[num_12] [numeric](12, 0) NULL,
[num_5_4] [numeric](5, 4) NULL
so the distinct data types are decimal,numeric, real,float,integer. the answer is still 5.
May 6, 2015 at 7:48 am
paul.knibbs (5/6/2015)
Orlando Colamatteo (5/5/2015)
The ANSI SQL standard defines NUMERIC and DECIMAL separately and there is a distinction in how each might behave different from one another however that is not the case in SQL Server's implementation of the standard. It would be interesting to hear from someone with knowledge of the design history, the actual SQL Server codebase, or who is privy to more of the internals to hear the story behind why the system types are different and why a pure synonym like INT for INTEGER was not implemented.I can't speak as someone with such knowledge, but if I were writing software that had to deal with a standard where NUMERIC and DECIMAL are defined separately, I'd be sure that it was possible to tell those two types apart internally. After all, what if some future version of the standard introduces a substantive difference between the two? If I've defined them to be the same internally, it's going to be a lot of work and break a lot of existing applications to split them out.
I would tend to agree. In designing a system to meet an established standard I too would consider hedging and leaving room for a divergence while still maintaining interface, no matter how unlikely a divergence might be after the initial implementation. I think supporting both types, even if only in terms of the metadata, might have been the hedge. "Internally", as it relates to the storage engine and query optimizer, by all evidence they seem to be indistinguishable. "Internally", as it relates to the object metadata, they are clearly distinguishable.
The implementation appears to be a carry-forward from Sybase where NUMERIC is documented as "identical to decimal" and "same as decimal" across multiple versions. I do not have access to a Sybase instance these days so cannot check how it stores metadata.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 6, 2015 at 7:54 am
CREATE TABLE test_numeric_parent (
thekey NUMERIC(10,0) NOT NULL PRIMARY KEY
);
CREATE TABLE test_decimal_parent (
thekey DECIMAL(10,0) NOT NULL PRIMARY KEY
);
CREATE TABLE test_numeric_child (
thekey NUMERIC(10,0) NOT NULL,
x INT NOT NULL PRIMARY KEY
);
CREATE TABLE test_decimal_child (
thekey DECIMAL(10,0) NOT NULL,
x INT NOT NULL PRIMARY KEY
);
CREATE TABLE test_decimal_child2 (
thekey DECIMAL(8,0) NOT NULL,
x INT NOT NULL PRIMARY KEY
);
ALTER TABLE test_numeric_child
ADD CONSTRAINT fk1 FOREIGN KEY (thekey)
REFERENCES test_decimal_parent(thekey);
ALTER TABLE test_decimal_child
ADD CONSTRAINT fk2 FOREIGN KEY (thekey)
REFERENCES test_numeric_parent(thekey);
ALTER TABLE test_decimal_child2
ADD CONSTRAINT fk2 FOREIGN KEY (thekey)
REFERENCES test_decimal_parent(thekey);
DROP TABLE test_numeric_child;
DROP TABLE test_decimal_child;
DROP TABLE test_decimal_parent;
DROP TABLE test_numeric_parent;
DROP TABLE test_decimal_child2;
All of the foreign keys above will fail. The first two fail with error 1778:
Msg 1778, Level 16, State 0, Line 24
Column 'test_decimal_parent.thekey' is not the same data type as referencing column 'test_numeric_child.thekey' in foreign key 'fk1'.
The third key fails with error 1753:
Msg 1753, Level 16, State 0, Line 32
Column 'test_decimal_parent.thekey' is not the same length or scale as referencing column 'test_decimal_child2.thekey' in foreign key 'fk2'. Columns participating in a foreign key relationship must be defined with the same length and scale.
NUMERIC and DECIMAL cannot be used interchangeably because they are different types.
As for whether a type with different length/scale is "the same type", that depends on what the question meant by "the same type."
May 6, 2015 at 9:52 am
Very good question, thanks.
May 6, 2015 at 10:38 am
Stephanie Giovannini (5/6/2015)
CREATE TABLE test_numeric_parent (thekey NUMERIC(10,0) NOT NULL PRIMARY KEY
);
CREATE TABLE test_decimal_parent (
thekey DECIMAL(10,0) NOT NULL PRIMARY KEY
);
CREATE TABLE test_numeric_child (
thekey NUMERIC(10,0) NOT NULL,
x INT NOT NULL PRIMARY KEY
);
CREATE TABLE test_decimal_child (
thekey DECIMAL(10,0) NOT NULL,
x INT NOT NULL PRIMARY KEY
);
CREATE TABLE test_decimal_child2 (
thekey DECIMAL(8,0) NOT NULL,
x INT NOT NULL PRIMARY KEY
);
ALTER TABLE test_numeric_child
ADD CONSTRAINT fk1 FOREIGN KEY (thekey)
REFERENCES test_decimal_parent(thekey);
ALTER TABLE test_decimal_child
ADD CONSTRAINT fk2 FOREIGN KEY (thekey)
REFERENCES test_numeric_parent(thekey);
ALTER TABLE test_decimal_child2
ADD CONSTRAINT fk2 FOREIGN KEY (thekey)
REFERENCES test_decimal_parent(thekey);
DROP TABLE test_numeric_child;
DROP TABLE test_decimal_child;
DROP TABLE test_decimal_parent;
DROP TABLE test_numeric_parent;
DROP TABLE test_decimal_child2;
All of the foreign keys above will fail. The first two fail with error 1778:
Msg 1778, Level 16, State 0, Line 24
Column 'test_decimal_parent.thekey' is not the same data type as referencing column 'test_numeric_child.thekey' in foreign key 'fk1'.
The third key fails with error 1753:
Msg 1753, Level 16, State 0, Line 32
Column 'test_decimal_parent.thekey' is not the same length or scale as referencing column 'test_decimal_child2.thekey' in foreign key 'fk2'. Columns participating in a foreign key relationship must be defined with the same length and scale.
NUMERIC and DECIMAL cannot be used interchangeably because they are different types.
As for whether a type with different length/scale is "the same type", that depends on what the question meant by "the same type."
I alluded to this behavior earlier when I said:
I concede there is some nuance built-in around the system metadata (and, I will add, around keys and type precedence) but in all I have found no evidence of a semantic difference.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 7, 2015 at 3:21 am
Stephanie Giovannini (5/5/2015)
While decimal and numeric are functionally equivalent, they are not the same type.
From head (no time to check the details now), I believe that the ANSI standard allows a subtle implementation difference between decimal and numeric - they use BCD representation, which is two digits per byte and a half-byte for the sign; IIRC they are the same when the total number of digits is odd, but when it's even the ANSI standard allows a DBMS implementation to treat one as if it has one more digit. But MS has not opted for this choice, so they are always exactly the same.
May 12, 2015 at 12:36 am
Surprised by the existence of the INTEGER type which is not appearing in the list of integer data types which includes only bigint , int , smallint and tinyint . I have learnt something today ( I will go on to use int instead INTEGER as it is shorter and more usual ).
Little remark : INTEGER is not appearing in the list of data type when I am using the "table creator" by right-click of Tables in SSMS 2014 ( on an instance of SQL Server Express 2014 ). Maybe an accepted data type but not officially...
Have you any news / explanatons about its omission in the BOL ?
Possible that I have not understood the BOL or even the meaning of the question ( usual with my poor understanding of the English language ... )
June 4, 2015 at 8:25 am
It is more difficult then I thought at first sight
September 14, 2015 at 1:53 pm
Nice one!
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply