SQL Server : SUM() weird behaviour
floating-point, sql-server, sum
Solution
The `FLOAT` and `REAL` data types are known as approximate data types. The behavior of `FLOAT` and `REAL` follows the `IEEE 754` specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; They store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the `FLOAT` and `REAL` data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types. Avoid using `FLOAT` or `REAL` columns in `WHERE` clause search conditions, especially with the `=` or `<>` operators. It is best to limit `FLOAT` and `REAL` columns with `>` or `<` comparisons.
Source of above statement
Problem
I run this example in SQL Server Management Studio: ``` SELECT CONVERT(REAL, -2101.12) n INTO #t SELECT * FROM #t SELECT SUM(n) FROM #t ``` The first `SELECT` creates a temp table `#t` with 1 column `n` of type real, and it puts 1 row in it with the value `-2101.12`. The second `SELECT` confirms that the table is created with the intended content and the result is: ``` n --------- -2101.12 ``` The third `SELECT` sums the only number that is there, but the result is: ``` -2101.1201171875 ``` So the question is: Where the `0.0001171875` comes from? EDIT: I know the lack of precision for the real and float data types, unfortunately I cannot change the database schema because of this. What surprise me though, is that I would expect to see also the extra decimals in the second select since it is supposed to be stored with that lack of precision. Since it does not happens on the second select, then why the sum function picks it up?