A few months ago a customer filed a bug where they had a table T1 with, a float column C1 among other things, and slightly more than a million rows.
They were running a TSQL query that basically looked like the following:
select sum(C1)from T1where -- some conditions that filter to about 50 rows.
Every time this query was executed, they got a different result. The varying result across executions, though not ideal were somewhat accepted as the drawback of rounding error, but the fact that the sum varied by more than 20% from execution to execution was not acceptable.
One has to be very careful when computing arithmetic operations on floating point numbers, like a sum. The order in which these additions are performed matters. And actually it can matter a lot. Take this example where the first sum is 0 and the second is 507904, the percentage difference between the two sums is not only large, it's infinite.
DECLARE @fl FLOAT = 100000000000000000000DECLARE @i SMALLINT = 0WHILE (@i < 100)BEGINSET @fl = @fl + CONVERT(float, 5000)SET @i = @i + 1ENDSET @fl = @fl - 100000000000000000000SELECT @flgoDECLARE @fl FLOAT = 0DECLARE @i SMALLINT = 0WHILE (@i < 100)BEGINSET @fl = @fl + CONVERT(float, 5000)SET @i = @i + 1ENDSET @fl = @fl + 100000000000000000000SET @fl = @fl - 100000000000000000000SELECT @flgo
Yes, rounding errors can have dramatic impact on a result, and the percentage difference in the result between two executions is not bounded to 1% or 5% or even 50%. A priori, there is no limit to how large the difference can be, it only depends on how the floating point rows are distributed and in which order they are added.
Now why this matters in practice when you are just doing "select SUM(c1) From T1" is because SQL Server may parallelize your query plan to run multiple threads on multiple CPUs or cores. Each thread would scan the disk to consume rows, add them, and each intermediate aggregate would then be added to produce the final sum. Depending on the load on the system, and the exact lifetime of each thread, the order in which rows will be added can vary from one execution of this query to the next. So if rounding is expected to be important, you can turn off parallelism by using OPTION(MAXDOP 1) in your query.
Rounding is not always an issue with floating point applications. If all the rows are positive floating points, with values that have relatively the same "magnitude", not too far from each other, maybe your application doesn't care about the 10th decimal point changing between two executions. But if your data doesn't follow that nice distribution, you need to care about operation ordering.