Forms Teach Deck Live

Aggregatfunktionen und der Datentyp FLOAT

Already a while ago I'm on a very specific problem in the use of FLOAT values \u200b\u200bencountered in the calculation of aggregates (see also here: https: / / connect.microsoft.com / SQLServer / feedback / ViewFeedback.aspx? FeedbackID = 465 147).

The following experiment shows once again what is it.
We first create a small test table:

use tempdb go


- Create test table
if (object_id ('t1') is not null) drop table t1


go create table T1
(
default x nchar (200) not null '#'
, float c1
not null, c2 decimal (38,5 )
)
go

The table has two numeric columns, one of type FLOAT, and another of type DECIMAL. In both columns, we enter the same values. Overall, added 400,000 lines by the following script:

- add 200,000 lines in the test table
declare @ x decimal (38,5) set @ x =
1000000000000000.91238
insert T1 (c1, c2) select @
x, - @ x
from (select row_number () over (order by current_timestamp) as rn
from sys.trace_event_bindings as a
,sys.trace_event_bindings as b) as rd
where rn <= 200000

-- Noch einmal 200.000 Zeilen.
-- Diesmal mit umgekehrten Vorzeichen
insert T1(c1,c2)
 select -@x, @x from
  (select row_number() over(order by current_timestamp) as rn 
     from sys.trace_event_bindings as a ,sys.trace_event_bindings as b) as rd where rn <= 200000
go

Ok, es existieren So now 400,000 lines. If we form the sum of the columns c1 and c2 on all lines, then the value should come out 0, is not it? Here is the relevant inquiry:

- Both sums should the value 0
provide select sum (c1) as SumFloat, sum (c2) as SumDecimal
from T1 union all select sum
(c1), sum (c2) from T1

union all select sum (c1), sum (c2) from t1 union all

select sum (c1), sum (c2) from t1 union all

select sum (c1), sum (c2) from T1

union all select sum (c1), sum (c2) from t1 union all

select sum (c1), sum (c2) from t1 union all

select sum (c1), sum (c2) from T1

union all select sum (c1), sum (c2) from t1 union all

select sum (c1), sum (c2) from t1 union all

select sum (c1), sum (c2) from T1

And here is the amazing result:


The sum of the DECIMAL value provides the expected result - namely, the value 0 For the FLOAT value, it does not look quite so good. Not only that, the result differs significantly (this is due to rounding errors in floating point), no: The value also changes from version to version! The result is not deterministic and that's pretty amazing, is not it?
The effect is due to the fact that the query is executed in parallel. Here's the execution plan is:


The data from the table that is read in several threads that are then brought together again at some point to a result (a sum). Here, the order of the merge is not guaranteed. Just because the rounding errors that occur on every execution are different. The commutative property of addition is not for the data type FLOAT!

why: Better to forego the use of FLOAT data types! Unfortunately, there can be situations where this is not possible. Thus, if, for example, SSAS for numerical Values in so-called write-back tables, the corresponding columns automatically with the data type FLOAT. The possible consequences you see in the example above.