# Extra Decimal Storage

 SQLA 17.0.4 Build 2053 (also seen in 12 and 16 though) It appears that fields defined as decimal (30,6) can store more than 6 decimal places. Take this example: ```drop table if exists zMyRate; create table zMyRate ( ID integer default autoincrement, Rate dec(30,6) NOT NULL, PRIMARY KEY (ID ASC)) ; insert into zMyRate (Rate) VALUES (1.234567), (8.901234), (5.678901), (2.34567899) ; SELECT ID, Rate, cast(Rate as dec(30,8)) as ExtraDecRate FROM zMyRate; ID,Rate,ExtraDecRate 1,1.234567,1.23456700 2,8.901234,8.90123400 3,5.678901,5.67890100 4,2.345679,2.34567900 ``` The last two digits on the fourth value are truncated during the insert as I would expect. But if you update these with a calculation that is not casted, the results are stored: ```Update zMyRate set Rate = Rate * 1.3456 ; SELECT ID, Rate, cast(Rate as dec(30,8)) as ExtraDecRate FROM zMyRate; ID,Rate,ExtraDecRate 1,1.661233,1.66123336 2,11.977500,11.97750047 3,7.641529,7.64152919 4,3.156346,3.15634566 ``` The extra decimal places are only returned if you cast the field as something defined with the extra decimals but not on the normal select as in column 2. What is interesting is that if you alter the table and define the field as decimal (12,6), this behavior stops and the numbers are truncated in both situations - insert and update with calculation. asked 02 Jun '17, 16:29 Siger Matt 3.2k●56●72●101 accept rate: 13% I'm guessing this has something to do with the fact the exact same storage is used for values that differ by one digit of actual precision and/or scale. The formula for storage requirements is thus, where p and s are the actual precision and scale of the value, not the declared precision and scale: 2 + TRUNCNUM ( ( p - s + 1 ) / 2, 0 ) + TRUNCNUM ( ( s + 1 ) / 2, 0 ) You can use DATALENGTH() as a check on your work. AFAIK this is the old-school "packed decimal" format where each digit is packed into 4 bits, but the field takes up a multiple of 8 bits, so half the time there's an extra nybble (nybble, get it? smaller than a byte? :) ```BEGIN DECLARE d5 DECIMAL ( 30, 5 ); DECLARE d6 DECIMAL ( 30, 6 ); SET d5 = 9.123; SET d6 = 9.1234; SELECT d5, DATALENGTH ( d5 ), 4 as p5, 3 as s5, 2 + TRUNCNUM ( ( p5 - s5 + 1 ) / 2, 0 ) + TRUNCNUM ( ( s5 + 1 ) / 2, 0 ) AS d5_length; SELECT d6, DATALENGTH ( d6 ), 5 as p6, 4 as s6, 2 + TRUNCNUM ( ( p6 - s6 + 1 ) / 2, 0 ) + TRUNCNUM ( ( s6 + 1 ) / 2, 0 ) AS d6_length; END; d5 DATALENGTH(d5) p5 s5 d5_length -------------------------------- -------------- ------ ------ --------- 9.12300 5 4 3 5 d6 DATALENGTH(d6) p6 s6 d6_length -------------------------------- -------------- ------ ------ --------- 9.123400 5 5 4 5 ``` I know this doesn't answer your question, but maybe it gets you further along the way :) (03 Jun '17, 05:11) Breck Carter
Be the first one to answer this question!
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×219
×3
×3

question asked: 02 Jun '17, 16:29

question was seen: 1,262 times

last updated: 03 Jun '17, 05:11