Burned by Floating Point Numbers

For those following along at home, you know that I've been working over the past few weeks with a big SQL Server database on a relatively small partition (until the new hardware arrives). Because of this, I've been trying to keep space consumption/database growth under strict control. When you're talking about millions of rows of data in a table, even 1 byte difference in the size of one numeric field translates to millions of bytes of extra disk space.

Well, one choice that I made was to represent decimal numbers using the "real" data type. This is a floating point decimal that uses 4 bytes per number. Other possible alternatives were all 8 bytes per number, so I essentially cut my storage in half (albeit, with some "acceptable" loss in precision).

While reconciling the results of my calculations to the set of control data, we came across a very good example of where this absolutely is the wrong choice of data type.

One field of the table contained Quantity information, and this was not always integer data (i.e., maybe it was weight). Well, my "real" numbers had no problems representing this data, for the most part. But, there was one point of sale where all of the sales were reversed (so that every record with a positive Quantity value would have a corresponding record with a negative Quantity value). The net quantity for this location was zero.

But, upon examination, my net quantity was coming out to be something like 0.000007472 due to errors introduced by the floating point math. Yeah, it's close to zero, but, it led to a serious flaw in my calculations.

You see, I was already trapping for Divide by Zero situations using a CASE clause:

SELECT CASE 
WHEN SumQty <> 0 
THEN (Qty/SumQty)* ValToSpread 
ELSE 0 
END

If it weren't for the floating point math, then this particular SumQty value would have been zero, and the result of the calculation would have been zero. But, in this case, the floating point's "zero" was a small fraction--not zero.

A tangent: Do you know why Divide by Zero is illegal? It's because the Y-axis (x=0) is an asymptote for the hyperbola y = a / x. This means that as the number that you're dividing by gets closer and closer to zero (i.e., a fraction approaching zero) that the quotient gets larger and larger (approaches infinity). So, dividing by zero is undefined because it's actually +/- infinity at the same time.

What's the point of this mathematical mumble-jumble? Well, the sum of my quantity was not zero, but rather a small fraction: 0.000007472. The result of dividing by this number turned out to be a very big number, when the true result that was needed in order to reconcile with the control data should have been zero. So, my results were way off of where they should have been (to the tune of millions of dollars). Whoops! Good thing this was just in QA!

To resolve this one case, I changed the quantity field's data type to a "decimal", which is a fixed-precision number (I can specify how many total digits and decimal digits are maintained). I still went from a 4-byte number to a 8-byte number, but the result of the calculation was dead on.

So, the lesson learned: sometimes it's quite necessary to make the trade-off of space and speed provided by floating point numbers for the accuracy of fixed precision.