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