Understanding the numeric and decimal data types

Question

Post reply

Understanding the numeric and decimal data types

michael.leach2015

SSCommitted

Points: 1597
More actions
October 4, 2018 at 9:42 pm

#415776

I am testing various values to better understand the numeric and decimal data types. First I believe they are the same. Is this correct?
Secondly, for both data types, I am using a precision of 5 and scale of 2. This means 5 total digits and 2 digits after the decimal point right?
If I insert an additional digit to the left of the decimal point (violating the precision) this causes an error. I understand this. So far so good.
However, when I insert an additional digit to the right of the decimal point (violating the precision and the scale), I don't get an error. Why not? I would expect an error in this case.

CREATE TABLE MyDataTypes
(
myBit bit,
myTinyInt tinyint,
mySmallInt smallint,
myInt int,
myBigInt bigint,
myNumeric numeric (5,2),
myDecimal decimal (5,2),
mySmallMoney smallmoney,
myMoney money
)
INSERT INTOMyDataTypes
(myBit, myTinyInt, mySmallInt, myInt, myBigInt, myNumeric, myDecimal, mySmallMoney, myMoney)
VALUES
(0,235,20587, 2100000000, 8000000000000000000, 845.5323, 525.3445, 210000.95952, 800000000000000.483048)

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

myNumeric
845.53

myDecimal
525.34

You don't need an error when extra digits are rounded for decimal with scale of 2.

Alex S

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

Yes, they are the same in SQL Server.

SQL chose not to treat extra decimal positions as en error, instead it implicitly rounds to the stored scale. I assume partly that's to allow for calcs to be stored back into the same value. For example, if you multiply (5,2) * (5,2), the result has 4 decimal places, and presumably you wouldn't want error from that. But that's just a guess, I don't know for sure.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 3

ScottPletcher - Friday, October 5, 2018 2:36 PM
Yes, they are the same in SQL Server.
SQL chose not to treat extra decimal positions as en error, instead it implicitly rounds to the stored scale. I assume partly that's to allow for calcs to be stored back into the same value. For example, if you multiply (5,2) * (5,2), the result has 4 decimal places, and presumably you wouldn't want error from that. But that's just a guess, I don't know for sure.

I think I've got it now. But just to be sure, I want to confirm something else. This time, suppose I am using data types of numeric (5,3) and decimal (5,3) in my columns. I try to insert one row of values. I get an error from this, but why? I can see two possibilities.

Possibility 1 (1st pic): SQL is counting the number of digits specified by the precision of 5 starting from the left. This means SQL would now encounter more digits than what was specified as the scale. This is causing the error.

Possibility 2 (2nd pic): SQL is starting with the number of digits specified by the scale (starting from the 8 in .318), then determining where to stop after it encounters the number of digits specified by the precision. SQL then realizes that there is an extra digit in the precision (the 5 in 583.) and because of the additional digit in the precision (number of digits to the left of the decimal point) this causes the error.

So just to make sure I fully understand this, what is the cause of the error, possibility #1 or possibility #2? or something else?

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

michael.leach2015 - Friday, October 5, 2018 7:18 PM
ScottPletcher - Friday, October 5, 2018 2:36 PM
Yes, they are the same in SQL Server.
SQL chose not to treat extra decimal positions as en error, instead it implicitly rounds to the stored scale. I assume partly that's to allow for calcs to be stored back into the same value. For example, if you multiply (5,2) * (5,2), the result has 4 decimal places, and presumably you wouldn't want error from that. But that's just a guess, I don't know for sure.
I think I've got it now. But just to be sure, I want to confirm something else. This time, suppose I am using data types of numeric (5,3) and decimal (5,3) in my columns. I try to insert one row of values. I get an error from this, but why? I can see two possibilities.
Possibility 1 (1st pic): SQL is counting the number of digits specified by the precision of 5 starting from the left. This means SQL would now encounter more digits than what was specified as the scale. This is causing the error.
Possibility 2 (2nd pic): SQL is starting with the number of digits specified by the scale (starting from the 8 in .318), then determining where to stop after it encounters the number of digits specified by the precision. SQL then realizes that there is an extra digit in the precision (the 5 in 583.) and because of the additional digit in the precision (number of digits to the left of the decimal point) this causes the error.
So just to make sure I fully understand this, what is the cause of the error, possibility #1 or possibility #2? or something else?

Internally a decimal (and numeric) are stored as an integer, there is an extra byte that stores the precision and scale. If you have a column defined as decimal(5,2) internally it would store 583.31 as 58331, the additional byte would store the information that the result needs to be shift right by 2 places. Giving 583.31.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

You might want to have a look at the following.
Precision, scale, and Length (Transact-SQL)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 6

michael.leach2015 - Thursday, October 4, 2018 9:42 PM

NUMERIC(s,p) and DECIMAL(s,p) are the same in SQL Server, which they inherited from Sybase. This was from UNIX and 16-bit hardware. In the ANSI/ISO standards, they are not the same. DECIMAL(s,p) allows more precision than was specified. NUMERIC(s,p) is exact. This goes back to the original ANSI X3H2 days. NUMERIC(s,p) was for COBOL, which stores NUMERIC(s,p) values in strings. DECIMAL(s,p) was for BCD, a popular storage format. Sometimes in COBOL and sometimes and other languages. I'm already being pedantic enough, so you might want to just look them up in Wikipedia.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 7

jcelko212 32090 - Monday, October 8, 2018 11:13 AM

michael.leach2015 - Thursday, October 4, 2018 9:42 PM
NUMERIC(s,p) and DECIMAL(s,p) are the same in SQL Server, which they inherited from Sybase. This was from UNIX and 16-bit hardware. In the ANSI/ISO standards, they are not the same. DECIMAL(s,p) allows more precision than was specified. NUMERIC(s,p) is exact. This goes back to the original ANSI X3H2 days. NUMERIC(s,p) was for COBOL, which stores NUMERIC(s,p) values in strings. DECIMAL(s,p) was for BCD, a popular storage format. Sometimes in COBOL and sometimes and other languages. I'm already being pedantic enough, so you might want to just look them up in Wikipedia.

The data type probably dates back to the 1945 ENIAC computer.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 8

Jonathan AC Roberts - Saturday, October 6, 2018 7:18 PM
michael.leach2015 - Friday, October 5, 2018 7:18 PM
ScottPletcher - Friday, October 5, 2018 2:36 PM
Yes, they are the same in SQL Server.
SQL chose not to treat extra decimal positions as en error, instead it implicitly rounds to the stored scale. I assume partly that's to allow for calcs to be stored back into the same value. For example, if you multiply (5,2) * (5,2), the result has 4 decimal places, and presumably you wouldn't want error from that. But that's just a guess, I don't know for sure.
I think I've got it now. But just to be sure, I want to confirm something else. This time, suppose I am using data types of numeric (5,3) and decimal (5,3) in my columns. I try to insert one row of values. I get an error from this, but why? I can see two possibilities.
Possibility 1 (1st pic): SQL is counting the number of digits specified by the precision of 5 starting from the left. This means SQL would now encounter more digits than what was specified as the scale. This is causing the error.
Possibility 2 (2nd pic): SQL is starting with the number of digits specified by the scale (starting from the 8 in .318), then determining where to stop after it encounters the number of digits specified by the precision. SQL then realizes that there is an extra digit in the precision (the 5 in 583.) and because of the additional digit in the precision (number of digits to the left of the decimal point) this causes the error.
So just to make sure I fully understand this, what is the cause of the error, possibility #1 or possibility #2? or something else?
Internally a decimal (and numeric) are stored as an integer, there is an extra byte that stores the precision and scale. If you have a column defined as decimal(5,2) internally it would store 583.31 as 58331, the additional byte would store the information that the result needs to be shift right by 2 places. Giving 583.31.

Ok. So just to make sure I understand what you are saying, let's say I have a column as decimal (5,2). How would the number 583.318834 be stored internally?

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 9

michael.leach2015 - Monday, October 8, 2018 7:41 PM
Ok. So just to make sure I understand what you are saying, let's say I have a column as decimal (5,2). How would the number 583.318834 be stored internally?

If you were to store it as decimal(9,6) It would be stored as 583318834 (which can be stored in a 4-byte two's compliment integer) with the extra byte having a value that instructs the number is shifted right by 6 decimal places, giving 583.318834. So it would need 5 bytes total to store it.
If you store it as decimal(5,2) it would store is as 58332 (also in a two's compliment 4 byte integer) with the additional byte telling it to be shifted right by 2 decimal places, giving 583.32.
Both variables take up 5 bytes, so you might just as well use decimal(9,6) as decimal(5,2)
If you were to store 583.32 in a decimal(9,6) it would store it as 583320000 with the additional byte holding the instruction to shit right by 6 decimal places. giving 583.320000

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 10

Jonathan AC Roberts - Monday, October 8, 2018 7:58 PM
michael.leach2015 - Monday, October 8, 2018 7:41 PM
Ok. So just to make sure I understand what you are saying, let's say I have a column as decimal (5,2). How would the number 583.318834 be stored internally?
If you were to store it as decimal(9,6) It would be stored as 583318834 (which can be stored in a 4-byte two's compliment integer) with the extra byte having a value that instructs the number is shifted right by 6 decimal places, giving 583.318834. So it would need 5 bytes total to store it.
If you store it as decimal(5,2) it would store is as 58332 (also in a two's compliment 4 byte integer) with the additional byte telling it to be shifted right by 2 decimal places, giving 583.32.
Both variables take up 5 bytes, so you might just as well use decimal(9,6) as decimal(5,2)
If you were to store 583.32 in a decimal(9,6) it would store it as 583320000 with the additional byte holding the instruction to shit right by 6 decimal places. giving 583.320000

Awesome explanation. Thank you.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 11

michael.leach2015 - Monday, October 8, 2018 9:43 PM
Jonathan AC Roberts - Monday, October 8, 2018 7:58 PM
michael.leach2015 - Monday, October 8, 2018 7:41 PM
Ok. So just to make sure I understand what you are saying, let's say I have a column as decimal (5,2). How would the number 583.318834 be stored internally?
If you were to store it as decimal(9,6) It would be stored as 583318834 (which can be stored in a 4-byte two's compliment integer) with the extra byte having a value that instructs the number is shifted right by 6 decimal places, giving 583.318834. So it would need 5 bytes total to store it.
If you store it as decimal(5,2) it would store is as 58332 (also in a two's compliment 4 byte integer) with the additional byte telling it to be shifted right by 2 decimal places, giving 583.32.
Both variables take up 5 bytes, so you might just as well use decimal(9,6) as decimal(5,2)
If you were to store 583.32 in a decimal(9,6) it would store it as 583320000 with the additional byte holding the instruction to shit right by 6 decimal places. giving 583.320000
Awesome explanation. Thank you.

As to storage size, that's not the only consideration. You also need to consider vardecimal format, which SQL Server uses to reduce bytes. Not all decimal values automatically require all 5 bytes. Adding extra digits could cost you extra bytes, since the data will compress less.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 12

ScottPletcher - Tuesday, October 9, 2018 9:27 AM
As to storage size, that's not the only consideration. You also need to consider vardecimal format, which SQL Server uses to reduce bytes. Not all decimal values automatically require all 5 bytes. Adding extra digits could cost you extra bytes, since the data will compress less.

Although DATALENGTH returns different values depending on the size of the item stored. When the data is stored on a table it uses no less space. So the data doesn't seem to be compressed when added to a table.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL) INSERT INTO #TempNumerics(n1) SELECT C FROM (VALUES (42949672.95), (42949672.96), (-42949672.95), (-42949672.96) ) T(C)

SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN FROM #TempNumerics;

IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16 CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL) INSERT INTO #TempNumerics16(n1) SELECT C FROM (VALUES (42949672.95), (42949672.96), (-42949672.95), (-42949672.96) ) T(C)

SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN FROM #TempNumerics16
See this link: https://www.sqlservercentral.com/Forums/FindPost1993475.aspx

ScottPletcher SSC Guru Points: 100949 More actions · Answer 13

Jonathan AC Roberts - Tuesday, October 9, 2018 9:38 AM
ScottPletcher - Tuesday, October 9, 2018 9:27 AM
As to storage size, that's not the only consideration. You also need to consider vardecimal format, which SQL Server uses to reduce bytes. Not all decimal values automatically require all 5 bytes. Adding extra digits could cost you extra bytes, since the data will compress less.
Although DATALENGTH returns different values depending on the size of the item stored. When the data is stored on a table it uses no less space. So the data doesn't seem to be compressed when added to a table.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL) INSERT INTO #TempNumerics(n1) SELECT C FROM (VALUES (42949672.95), (42949672.96), (-42949672.95), (-42949672.96) ) T(C)
SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN FROM #TempNumerics;
IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16 CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL) INSERT INTO #TempNumerics16(n1) SELECT C FROM (VALUES (42949672.95), (42949672.96), (-42949672.95), (-42949672.96) ) T(C)
SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN FROM #TempNumerics16
See this link: https://www.sqlservercentral.com/Forums/FindPost1993475.aspx

That's not really logical. The vardecimal format must be able to save space in specific cases or it wouldn't exist. Thus, to me the general rule stays the same: don't add digits you don't really need to decimal values assuming they won't require extra byte(s), because they could.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 14

ScottPletcher - Tuesday, October 9, 2018 9:50 AM
Jonathan AC Roberts - Tuesday, October 9, 2018 9:38 AM
ScottPletcher - Tuesday, October 9, 2018 9:27 AM
As to storage size, that's not the only consideration. You also need to consider vardecimal format, which SQL Server uses to reduce bytes. Not all decimal values automatically require all 5 bytes. Adding extra digits could cost you extra bytes, since the data will compress less.
Although DATALENGTH returns different values depending on the size of the item stored. When the data is stored on a table it uses no less space. So the data doesn't seem to be compressed when added to a table.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL) INSERT INTO #TempNumerics(n1) SELECT C FROM (VALUES (42949672.95), (42949672.96), (-42949672.95), (-42949672.96) ) T(C)
SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN FROM #TempNumerics;
IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16 CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL) INSERT INTO #TempNumerics16(n1) SELECT C FROM (VALUES (42949672.95), (42949672.96), (-42949672.95), (-42949672.96) ) T(C)
SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN FROM #TempNumerics16
See this link: https://www.sqlservercentral.com/Forums/FindPost1993475.aspx
That's not really logical. The vardecimal format must be able to save space in specific cases or it wouldn't exist. Thus, to me the general rule stays the same: don't add digits you don't really need to decimal values assuming they won't require extra byte(s), because they could.

vardecimal looks like it's an option that you have to enable:
https://technet.microsoft.com/en-us/library/bb508963(v=sql.90).aspx