Friday, November 26, 2010

SQL Precision (Float vs Decimal) and Computation

If we have a computation of (a/b*c), in decimal SQL datatype it will not give the same result as (a*c/b)
Correct value can be obtained using this sequence: multiply first then divide ( (a*c/b).
Trying the computation in calculator, the actual value will be value of (a*c/b)

Here's the sample:

DECLARE @a as DECIMAL(38,10), @b as DECIMAL(38,10), @dec as decimal(38,10)
declare @flt as float
SET @a = 5094450
SET @b = 5225985.8002
SET @dec= 2504000
SET @flt= 2504000
SELECT @dec*@b/@a -- 2568651.855195
SELECT (@dec/@a)*@b -- 2568650.410585
SELECT @flt*@b/@a -- 2568651.85519552