THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in Alexander Kuznetsov (Entire Site) Search

# For better precision cast decimals before calculations

I frequently do calculations against decimal values. In some cases casting decimal values to float ASAP, prior to any calculations, yields better accuracy.  For example, in the following script both expressions should return 12100.0:

SELECT POWER(POWER(12100.0, 0.01),100), POWER(POWER(CAST(12100.0 AS FLOAT), 0.01),100)

--------------------------------------- ----------------------

13780.6                                 12100

Another example demonstrates poor accuracy of averaging decimals as compared to averaging floats:

CREATE TABLE #t(d DECIMAL(6,1));

GO

INSERT INTO #t

SELECT 1 UNION ALL SELECT 0 UNION ALL SELECT 0;

DECLARE @avg FLOAT;

SELECT @avg = AVG(d) FROM #t;

SELECT @avg AS [avg], @avg*3 AS imprecize_avg_by_3;

SELECT @avg = AVG(CAST(d AS FLOAT)) FROM #t;

SELECT @avg AS [avg], @avg*3 AS precize_avg_by_3;

avg                    imprecize_avg_by_3

---------------------- ----------------------

0.333333               0.999999

(1 row(s) affected)

avg                    precize_avg_by_3

---------------------- ----------------------

0.333333333333333      1

Of course, explicit casting to a decimal with higher precision is also an option:

DECLARE @avg FLOAT;

SELECT @avg = AVG(CAST(d AS DECIMAL(38,18))) FROM #t;

SELECT @avg AS [avg], @avg*3 AS precize_avg_by_3;

avg                    precize_avg_by_3

---------------------- ----------------------

0.333333333333333      1

Published Saturday, December 20, 2008 10:38 PM by Alexander Kuznetsov

#### David L. Penton said:

Calculations are all about significant figures.  Prior to your final calculation, as long as you properly carry your significant figures (http://en.wikipedia.org/wiki/Significant_figures) you are fine.  Then you can round to what is appropriate for your final answer.

December 20, 2008 11:48 PM

#### AK said:

Well, prior to my final calculation:

POWER(POWER(12100.0, 0.01),100)

I carry my significant figures all right, but the result is 13.88% wrong - 13780.6 instead of 12100 ;)

December 21, 2008 10:13 AM

#### febrian said:

What language is it?

February 4, 2016 8:59 PM

#### nairbef said:

@febrian Are you for real?

August 22, 2016 12:19 AM

#### BT said:

Thank you, this was the most concise and clearest answer out there.  Appreciate the SQL for proof as well!  Much appreciated.

February 9, 2017 4:22 PM

#### PP said:

I suggest this says more about the POWER function than anything else.

According to MSDN, the POWER function expects a parameter of type float. Furthermore it returns the same type as entered.

In your example 12100.0 is decimal (6,1). The power function converts this to a float BUT it returns a value that is also constrained to decimal (6,1) hence the behaviour you see. As means of demonstration try:

SELECT POWER(12100.00000000000000,0.01)

March 28, 2017 10:34 AM
New Comments to this post are disabled