The Basics - Float or Dec?

This is my blog on databases, and as such I try and it keep it confined to IT and business. But there are parallels to my other job as a martial arts coach.

In jiu-jitsu, if you do not have solid basics, then you will lose. All the flashy moves in the world won't help if you don't know the finish.

And it's the same in the world of databases. I've worked on a few systems where the front end had all sorts of neat wizardry, but the engine was terrible. Here is one such example:

A financial controller came to be one day asking why some of her reports were out by one cent. Everything added up, but the final figure on the report was $0.01!

After looking at the database definitions, I saw the obvious (to me). The original designer had created the currency fields as floating point decimals!

And sadly, this is common. Because it's so easy to create a table (in for example Microsoft SQL server), then people who have not really grasped the intricacies of data types will do it, and quite often get it wrong.

Here is a brief example to demonstrate the issue of using Floats compared to Decimals:

(Copy paste the following into SSMS)

DECLARE @Num1 decimal(10,2), @Num2 decimal(10,2), @Num3 decimal(10,2);
SET @Num1 = 54; 
SET @Num2 = 3.1; 
SET @Num3 = 0 + @Num1 + @Num2; 
SELECT @Num3 AS TotalAsDecimal
SELECT @Num3 - @Num1 - @Num2 AS "Should be 0";

GO

DECLARE @Num1 float, @Num2 float, @Num3 float;
SET @Num1 = 54; 
SET @Num2 = 3.1; 
SET @Num3 = 0 + @Num1 + @Num2; 
SELECT @Num3 AS TotalAsFloat
SELECT @Num3 - @Num1 - @Num2 AS "Should be 0";

Both these two blocks do the same thing, the only difference is in the datatypes.

Here are the results:

To understand why this simple mathematical example returns a "wrong" answer, I suggest you watch this great video put out by Computerphile. The presenter does a great job of explaining how floating point numbers are stored on disk, and how this error actually happens.

So, until next time .... study the basics, and get them right! If you get the basics wrong, it will be an expensive problem to fix later down the road.