I recently came across a concrete example of something that has always been maintained as a best practice – when working on items that are sensitive to decimal precision, always use precise data-types like DECIMAL instead of FLOAT or REAL.
The following example clearly demonstrates that using a FLOAT in mathematical calculations may change the outcome of the computation. For financial calculations, this would mean incorrect reporting of revenues.
USE tempdb; GO DECLARE @floatValue FLOAT = 1.5, @decimalValue DECIMAL(5,2) = 1.5, @multiplicationFactor INT = 159; SELECT @floatValue AS FloatValue, @decimalValue AS DecimalValue, (@multiplicationFactor*(@floatValue/100)) AS FloatBasedValue, (@multiplicationFactor*(@decimalValue/100)) AS DecimalBasedValue, ROUND((@multiplicationFactor*(@floatValue/100)),2) AS RoundedFloatBasedValue, ROUND((@multiplicationFactor*(@decimalValue/100)),2) AS RoundedDecimalBasedValue;
The output of this simple computation is provided in the screenshot below:
As can clearly be seen, the computations performed using an imprecise datatype (FLOAT) result in a rounding error. When compounded over multiple derived calculations, this can have a serious impact on the end result.
The conclusion therefore is simply that it is always a best practice to use precise datatypes when working with financial calculations.
- FLOAT & REAL [MSDN Link]
Until we meet next time,
Be courteous. Drive responsibly.