Solved Do Calculations Inside Decimal Data-Type Suffer Memory Truncation? (2 Viewers)

[dalski]

Will the specification of the Decimal data-type apply to all calculations wrapped inside the Decimal data-type & each calculation inherit the accuracy specified by the parent Decimal type (26 decimal points in e.g.). I'm hoping it does because I have complex calculations which I do not want to obfuscate by specifying the nr of decimal points.

FUNCTION fnName
RETURNS Decimal (38,26)
BEGIN
RETURN
    ((A*B)+(C*D)) / A
END;

Or must one specify rounding to avoid memory truncation?

FUNCTION fnName
RETURNS Decimal (38,26)
BEGIN
RETURN
   ROUND(
    (ROUND(A*B,26)+ROUND(C*D,26)) / A
  ,26)
END;

In the above I do not think it is necessary to specify the rounding on the final division by A as I interpret that the parent Decimal data-type (38,26) would apply here. But to clarify the question I leave it like that.

 

[GPGeorge]

It depends a lot on how precise your calculations need to be. I'm no mathematician, but I do know that rounding your inputs prior to operating on them mathmatically is going to yield different results than rounding the output at the end.

I'd try it out on a good sample of your existing data, though, to evaluate what the impact would be.

 

[dalski]

I'm no mathematician either, but I need to be accurate to 26 decimal places to minimise nested compounding; playing it safe to minimize problems. I do need big numbers mind as the calculations need to be as accurate as possible with linear regression (probably the wrong term).

It's quite a pig to test as Excel displays scientific notation on these kind of numbers.
Part of me thinks inheritance should apply to all numbers wrapped within the data-type, part of me thinks decimal point specification will only apply to the result of the function, & unpredictable results within each calculation.

Excel suffers from memory truncation & the above method is required to avoid it, but I'm praying this is not the case in MSSQL.

 

[GPGeorge]

I'm sure someone with deeper mathematical knowledge will be along soon.

I'll just reiterate that running both versions on a representative data sample, and comparing the resultsets could be helpful.

Create both functions in SQL Server, with names that clarify how rounding is applied.

I think it should be pretty straightforward to create a query that returns three columns, one for each of the functions operating on your sample data, and a third to compare the two columns to see if they are equal or not.

 

[RonPaii]

Will the specification of the Decimal data-type apply to all calculations wrapped inside the Decimal data-type & each calculation inherit the accuracy specified by the parent Decimal type (26 decimal points in e.g.). I'm hoping it does because I have complex calculations which I do not want to obfuscate by specifying the nr of decimal points.

FUNCTION fnName
RETURNS Decimal (38,26)
BEGIN
RETURN
    ((A*B)+(C*D)) / A
END;

Or must one specify rounding to avoid memory truncation?

FUNCTION fnName
RETURNS Decimal (38,26)
BEGIN
RETURN
   ROUND(
    (ROUND(A*B,26)+ROUND(C*D,26)) / A
  ,26)
END;

In the above I do not think it is necessary to specify the rounding on the final division by A as I interpret that the parent Decimal data-type (38,26) would apply here. But to clarify the question I leave it like that.

Intermediate rounding can case loss of precision.
I would think the intermediate calculations like (A*B) would be determined by the types of A and B but even if they are Decimal(38,26) unless one or both had 26 digits to the right of the decimal or one or both are less than 1, rounding to 26 will have no affect. Now if one of the numbers has 12 digits to the left of the decimal point and the other is greater then one, you may have an overflow error.

 

[dalski]

Thanks Ron, most intermediate calc's are Decimal(38,26) so it seems type-inheritance applies. I'll round intermediate calc's where <> (38,26).

Overflow Errors - thanks, there's always going to be a limit so I'm not too worried about this as I'm designing around reasonable spec's for the application's use.

 

[The_Doc_Man]

When Excel drops into scientific notation, it has probably also dropped into DOUBLE data type for internal operations. More specifically, IEEE 754, which is approximately 15 decimal digits of precision on a 53-bit mantissa.

In Access, DECIMAL is a 17-byte structure for which you can specify the total number of digits and the number of implied fractional digits because the decimal point is SCALED, not maintained in the same way as SINGLE/DOUBLE (which has an explicit exponent). CURRENCY is similar to the Access DECIMAL(19,4) format i.e. it is LIKE a typecast of DECIMAL. (But it isn't, really.)

In SQL Server, you can get up to 38 digits for DECIMAL but it is otherwise the same (conceptually) as the Access version of DECIMAL. So the limits are different but the same rules apply.

DECIMAL data type is subject to truncation and rounding problems even though internally it is managed via integer math instructions doing a simulation of the old methods you learned in school to do basic math. Like adding the numbers one column at a time - except that for DECIMAL data types, the columns are binary components of the extended number.

 

[dalski]

Thanks Doc, Access will be fine with 17 decimal points to display enough accuracy, it is the calculations done in the back-end where i need the accuracy.

Are you saying that I need to explicitly round all intermediate calculations regardless of their specified decimal point spec? An intermediate calculation regardless of decimal point spec will be unreliable & it's decimal point spec needs to be defined by wrapping it in a Round function? I think this is what you're saying.

 

[The_Doc_Man]

No, actually I'm not. Addition and subtraction do not cause fractional truncation or rounding issues for DECIMAL. If you are doing a lot of multiplication and division, however, just remember that - regardless of what you are doing, when the formula gets to the part where it stores a number to the left of the equal sign, at that point IF you had fractional parts for both math operands, there is the POSSIBILITY that you would gain or lose decimal places. If you had no fractional digits for something, multiplication doesn't make MUCH of a difference, but division always affects fractions more strongly.

Let's say you had a CURRENCY, which behaves similarly - though not identically - to DECIMAL. Currency ACTS like DECIMAL(19,4). Let's do repeated divisions by 2 of a DECIMAL number to define what is going to happen.

Start with 1, divide by 2 = 0.5 ... and remember there is no mathematics difference between 1/2 and 1 * 0.5, so this division is just like multiplication by fractions.
Repeat the division 0.5/2 = 0.25 (or 0.5 * 0.5, same result)
Then 0.25/2 = 0.125
Then 0.125/2 = 0.0625 .... still 4 digits.
Then 0.0625/2 = 0.03125 - and for a variable with a fixed number of decimal places, you now have 5 digits but only 4 places to put them. So... what is the correct answer according to your rules? As near as I can tell for both Access and for SQL Server DECIMAL data types, the answer would be stored via Banker's rounding (to nearest even number) if you did nothing to override that.

Multiplication by an integer usually has no effect on number of digits to the right of the decimal point. Division probably will affect the required digits, and any action involving fractions (like a percentage of some value) will probably increase the risk of decimal point adjustment and/or some other kind of adjustment.

The simple-minded point I was making is that either division or multiplication have the potential to extend your decimal places in the true number (i.e. independent of computer issues) but your chosen data variable has to store within its defined limits. I don't KNOW what you need to do because you know the environment of your problem - I don't. Whatever you do, it does require you to be aware of the operations that will extend the apparent decimal places.

 

[dalski]

Thanks Doc, I am aware of the enigma with multiplication & division, but thank you for prompting had I not been aware. Don't worry about the possibility of different data-types being used as terms within the expression; I have this covered. I also have it covered where the decimal places specification is inconsistent & the topic is not regarding Banker's Rounding, it is just regarding floating-point arithmetic; which I don't think is relevant here as I think Decimal's data-type in MSSQL is not a floating point, but a guaranteed decimal precision (like Currency); more accurate but costly with more storage size required; but I really need to be sure what the situation is so forgive my superfluity.

@RonPaii has suggested that the product's/ quotient's of intermediate calc's decimal places will be inherited through the algebraic terms used within the expression (so if they're both 26 decimal points the product/ quotient will be 26 decimal places). Disregard the possibility of different decimal point formats/ data-types or the possibility of overflow/ bankers rounding...; I have this covered (not worried about bankers rounding). I am just trying to ascertain if the product/ quotient of intermediate calc's need to have their decimal point specified by wrapping it inside a Rounding function?

If it is like Excel (which I don't think it is as I think the decimal precision accuracy is guaranteed) but if it is like Excel then I need to wrap all intermediary calc's within a Round() function as the recommended method by Microsoft. I'm pretty sure in the past I have encountered unanticipated memory truncation working with surprisingly small numbers due to floating point arithmetic. I know you are well aware of this (not telling you how to suck eggs, just trying to clarify).

From an algebraic perspective - my question is - will the algebraic terms used within an expression inherit their decimal point specification of their datatype specification? Assume they are the same data type Decimal(38,26) for this e.g.

 

[RonPaii]

According to Microsoft SQL documentation the intermediate results will have the same type as the components.

Precision, scale, and length (Transact-SQL) - SQL Server

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number.

learn.microsoft.com

 

[The_Doc_Man]

(not telling you how to suck eggs, just trying to clarify).

Interesting. Are/were you a reader of Robert A Heinlein? Because he used a variant of that phrase more than once. "Don't tell grandpa how to suck eggs." Or did you pick that up from a "country cousin"? I've heard it from a few of my Alabama relatives.

According to Microsoft SQL documentation the intermediate results will have the same type as the components.

Yes. The phenomenon is called "LET Coercion" and it is how VBA tries to make computational results consistent. The LET Coercion merely forces everything to be of the "highest" data type in the formula , thus keeping an "apples to apples" operation. I think it is the equivalent of putting a conversion function around all of the "lower" data types to bring them up to the highest data type.

Of course it is always up to you to decide what is the proper treatment for your data. So if you're happy with where you are going with this, round it off to your heart's contentment.