SQL Server rounding Error, Giving different values

Question

I have a Stored Procedure that does a lots of calculation, stores the results in several temporary table. Finally calculating the sum and rounding to two decimal and stores in a temporary table and selects that.

All the intermediate and final temporary table has datatype float for the column of concern.

original Scenario:

Declare @Intermediate table
{
 --several other columns

Labor float

--several other columns
};

---Lots of calculation ---xx-----

Declare @Final table
{
 --several other columns

LaborTotal float

--several other columns
};

INSERT INTO @Final  SELECT ROUND(ISNULL((SELECT SUM([Labor]) FROM @Intermediate ),0),2)  AS LaborTotal;

SELECT * FROM @Final;

Result: 7585.22  --> when rounded  //Here is the error Expecting 7585.23
        7585.225 --> when not rounded

TestCases :

   DECLARE @test float = 7585.225;
   SELECT ROUND(@test,2) AS Result; --> results 7585.23

   SELECT ROUND(7585.225,2) AS Result --> results 7585.23

Inserted individual values to a temporary table, and then calculated the sum

DECLARE @TmpTable table
(
 MaterialAmount float
 ,LaborAmount float
);

INSERT INTO @TmpTable VALUES (12.10,1218.75);
INSERT INTO @TmpTable VALUES (12.10,1090.125);
INSERT INTO @TmpTable VALUES (12.10,900);
INSERT INTO @TmpTable VALUES (12.10,1632.6);
INSERT INTO @TmpTable VALUES (12.10,1625);
INSERT INTO @TmpTable VALUES (12.10,1118.75);


SELECT ROUND(ISNULL((SELECT SUM(MaterialAmount) FROM @TmpTable), 0),2) AS MatSum,
       ISNULL((SELECT SUM(LaborAmount) FROM @TmpTable), 0) AS LabSumUnrounded, --> 7585.225
       ROUND(ISNULL((SELECT SUM(LaborAmount) FROM @TmpTable), 0),2) AS LabSum;  --> 7585.23

SELECT ROUND(SUM(MaterialAmount),2),
       ROUND(SUM(LaborAmount),2)   ---> 7585.23
   FROM @TmpTable;

Any idea/suggestion why i am getting 0.01 difference in my original scenario, while getting exact values in all my testcases ? Thanks in advance.

Answer 1

This is because you are using float database type.

Float should not be used to represent values that require precision, since they are stored as approximations, different manipulations can give you different results.

In sql server you can use decimal and numeric data types for numerical precision: http://msdn.microsoft.com/en-us/library/ms187746.aspx