FLOAT vs Decimal in math functions.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FLOAT vs Decimal in math functions.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-19 : 08:30:40

I have a concern that I don't know how to verify if it will be a issue.

I started doing some research on Float(Base 2) and Decimal(base 10), and understand how float can not accuratly illustrate all decimal scenerios.

Because of this it raised another concern of why then is it used in math functions instead of decimal base 10 such as the POWER function, if it's not as accurate?

I need to use the Power function with large decimals, and I am trying to figure out if there are scenerios where due to a decimal not being able to be represented in a float, if it can incorrectly return results.

If so, is there another method to raise to a power using a more precise decimal datatype?

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-19 : 12:33:03

It a precision issue.
Is exactness of decimals criticial, use NUMERIC or DECIMAL datatype, and if large numbers is used and the decimal part is not that important, go for FLOAT.

Also, is there a chance you can do your math logarithmic?

select power(2, 7), EXP(7 * log(2))

E 12°55'05.63"
N 56°04'39.26"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-19 : 22:08:20

The question I guess is why wouldn't sql use decimal types rather than a binary type in it's built-in math functions such as Power?

out of the 3 queries

The first I would need to assume is accurate and the other are not due to float not being able to store the # properly.


SELECT CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) 
SELECT power(convert(decimal(38,15),107.915),5)
select EXP(5 * log(107.915))

Is there any way to get the power of a number not using a base 2, so the results that are returned are precise?

thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-20 : 11:49:53

SELECT 14635550929.788643900446875

E 12°55'05.63"
N 56°04'39.26"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-21 : 04:09:35

Yes, that is the # I was looking to get to, but is there anyway to have sql calculate it from the 107.915?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-21 : 04:09:50

quote:
Originally posted by Vinnie881
The question I guess is why wouldn't sql use decimal types rather than a binary type in it's built-in math functions such as Power?...

Virtually all programming languages use binary for floating point operations for a very simple reason: floating point operations are built that way direcly in the processor. POWER and the other math function take advantage of the built-in floating point operations.

When Intel starts supplying processors with base 10 floating point registers and built-in base 10 floating point operations you might get your wish. But I wouldn't count on seeing that anytime soon.

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-20 : 13:56:34

This just came up again, is the consensus that there is no way to accurately get 30 decimal places in the above scenario via sql due to base 2 covnersions?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-20 : 14:17:07

http://download.oracle.com/docs/cd/E19957-01/806-3568/ncg_goldberg.html
http://floating-point-gui.de/ (shorter version)

And to be realistic, 28 decimal places lets you measure the distance from the sun to Pluto in atomic radii. In other words, it's meaninglessly precise.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-20 : 14:36:54

So the follow-up question is how do I get 107.915 to the 5th power with 28 decimal places? I can only accurately get 5 before it becomes incorrect.

The correct number should be this 14635550929.788643900446875 as Peso has shown, but all methods below do not produce that.

SELECT CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915)
SELECT power(convert(decimal(38,15),107.915),5)
select EXP(5 * log(107.915))

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-20 : 14:50:00

I would say the short answer is, you can't in T-SQL, at least not easily. You might have to go with a CLR data type to get higher precision, and you'll have to use CLR methods to calculate it.

The follow-up question to your follow-up is: why do you need it to 28 decimal places? vs. 27? or 20? What specifically are you calculating that requires that precision?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-20 : 15:06:49

I don't require any specific number of decimal places, I am simply trying to utilize the most accurate calculations. In your example of how 28 decimal places can measure the distance from the sun to Pluto in atomic radii, which as you stated correctly is accurate for virtually anything, but when we are actually dealing with 5 decimal places it now gets in that gray area which is not as easy to classify as "meaninglessly precise".

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-20 : 15:18:00

I can't say I completely agree, because you also have 11 digits preceding the decimal point, so if you're accurate to 4 decimals you're accurate to 1 part in 1 quadrillion.

Regardless of my opinion, I don't think you can do much better with the native SQL datatypes and functions. It was never intended as a serious math programming language. You'll probably have to go to Mathematica or similar math package.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-20 : 15:48:30

I understand your point of view. However, digits to the left of the decimal can not be weighted the same as the decimal digits, because a float data-type has no issues accurately calculating those leading digits.

Hope this illustrates better:

SELECT CONVERT(decimal(38,30), 1.88823) * CONVERT(decimal(38,30), 1.88823) * CONVERT(decimal(38,30), 1.88823) * CONVERT(decimal(38,30), 1.88823) * CONVERT(decimal(38,30), 1.88823)
SELECT power(convert(decimal(38,15),1.88823),5)
select EXP(5 * log(1.88823))

Now there is only 2 digits to the left but still with varying results in the decimal, so 1 part in 1 quadrillion is not universally correct (it's not really correct in the first example either since you are basing the incorrect part on the number output and not the calculation itself where that incorrect position may vary). With a little effort I could very likely find a numbers with 1 digit to the left and incorrect decimal representation on the 5th decimal using this formula.

I am not disagreeing with you in any way as far as your logic, but I am just trying to evaluate the risk of using the formula.

Thanks for your help.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-07-20 : 16:45:34

quote:
Originally posted by Vinnie881

So the follow-up question is how do I get 107.915 to the 5th power with 28 decimal places? I can only accurately get 5 before it becomes incorrect.

The correct number should be this 14635550929.788643900446875 as Peso has shown, but all methods below do not produce that.

SELECT CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915)
SELECT power(convert(decimal(38,15),107.915),5)
select EXP(5 * log(107.915))

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

This worked OK for me

select x = 107.915*107.915*107.915*107.915*107.915

Results:

x                                    
------------------------------------ 
         14635550929.788643900446875

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-21 : 11:55:50

That works, thanks!

I however do not understand why if I explicitly convert to ensure the proper datatype is used, it then does not work?

declare @i as numeric(38,30)
select @i=107.915 *107.915*107.915*107.915*107.915
select @i
--OK So that works and the results/values are treated as a numeric datatype.

SELECT CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915) * CONVERT(decimal(38,30), 107.915)
--shouldn't this give the same results as your example?

SELECT CONVERT(numeric(38,30), 107.915) * CONVERT(numeric(38,30), 107.915) * CONVERT(numeric(38,30), 107.915) * CONVERT(numeric(38,30), 107.915) * CONVERT(numeric(38,30), 107.915)
--Then I did this test since we know the numeric type is working, and it STILL it does not work.

It seems that the issue is the explicit conversion, but can you explain why this has any effect, it is not making sense to me?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-22 : 12:33:11

Hey Vinnie,

I was looking at this and it struck me as very odd too. I did a little investigation and hhat is happening can be expained by the rules that SQL applys when performaing math on numerics.

quote:
The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Here is a link:
http://msdn.microsoft.com/en-us/library/ms190476.aspx

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-22 : 15:13:23

I appreciate you looking into this and it sheds a little clearity, but that rule to me seems like it should equally effect

select 107.915*107.915

the same way it effects

select CONVERT(numeric(38,30), 107.915) * CONVERT(numeric(38,30), 107.915)

and it does not seem to.

Thanks again, this might be one of those issues I will need to just come to grips with I might never fully understand without seeing the sourcecode.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-22 : 16:25:00

If you think about it they are different. I'm not 100% sure what SQL does internally, but 107.915 can be expressed as a NUMERIC(6,3). If you multiply NUMERIC(38,30) * NUMERIC(38,30) you get a NUMERIC(77, 60) using the math on the MSDN site (38+38+1, 30+30). What SQL actuall does, I don't know, but it has to conver the (77, 60) to someting it can handle, which is why it's truncating.

The other thing I wasn't expecting is if you try to cast that result to a NUMER(38,30) you get a arithmetic overflow error:

SELECT 
    CAST(
        CONVERT(numeric(38,30), 107.915) 
        * CONVERT(numeric(38,30), 107.915) 
        * CONVERT(numeric(38,30), 107.915) 
        * CONVERT(numeric(38,30), 107.915) 
        * CONVERT(numeric(38,30), 107.915) 
        AS NUMERIC(38,30)
        )

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-22 : 20:51:49

That is the issue, thanks!

I just verified

SELECT
CONVERT(decimal(6,3), 107.915)
* CONVERT(decimal(6,3), 107.915)
* CONVERT(decimal(6,3), 107.915)
* CONVERT(decimal(6,3), 107.915)
* CONVERT(decimal(6,3), 107.915)

does in fact return

14635550929.788643900446875

Now all I need to do take a value from a decimal(38,30) column, convert it down to the lowest possible decimal and then do the math...

A function with a dynamic exec, a little iteration, and a whole lot of messy coding should do the trick:)

Thanks very much for finding that and explaining. To me it would be much more logical if SQL auto converted down to the lowest possible decimal/numeric length in math operations since it can only add onto the amount of accurate digits returned with no obvious drawback, (I can not see performance lag being anything significant), but all and all now that I know the approach to use and why it happens, It helps out a lot!

Thanks again!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources