Query on Float DataType may return inconsistent result

Let us try following T-SQL script on SQL Server:

declare @a float =300000000000000000000000000000000000
select @a + 50 -@a
select @a -@a +50
go

Guess what is the result?  the first is 0. and the second result  is 50.

Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. When you add a small number with a very big number,  the small number might just lost in the end result.

As a end-user, we want consistent and correct result.  However,  float data type has no correct result since the value is not accurate.  Query result on float data type is also not consistent as well.  In the above example, we change the order of + and – operator, we will see different result.   Suppose we want aggregate float values, such as sum(floatcolumn),  SQL Server may choose to parallel scan the whole table with different thread, and sum up the result together.  In this case, the order of data values are random, and the end-result will be random. In one of our customer’s case,  he run the same query multiple times, and each time he get a totally different result.

So, please try to avoid using float data type, especially  you want to do some aggregation on float types.  A workaround is to covert the type to a numeric type before doing aggregate.  For example, support X is float

you can use  sum(cast(x as decimal(30,4))) to get consistent result