This is my first QotD, and I'm pleased that it's gone quite well. It was prompted by outrage at missing out on points for another QotD (Sep 23, 2008, Accessing and changing data 2008) where it was deemed that the SqlServer 2008 GROUP BY GROUPING SETS was not the same as equivalent GROUP BY code because "Aggregates on floating-point numbers might return slightly different results." I protested but didn't get my 2 points back. So I wrote this QotD to prove my point, that identical aggregate code on an identical set of FLOAT numbers can ALSO produce "slightly different results", depending on the order of execution (which *should* be irrelevant in a perfect theoretical world without truncation).
BTW playing with the UNION & UNION ALL can reverse the results, but they will always (in my experience) produce the 2 differing results. Others noticed similar effects by sorting etc.
Carlo Romagnano (10/13/2008)
Main rule is never to use float, because of unwanted effects:Here the version with decimal. It always is correct.
It always is correct WITH THIS SET OF DATA. Try adding a couple of zero(e)s at the end of each of the 3 numbers and watch "Msg 8115, Level 16, State 6, Line 12 - Arithmetic overflow error converting float to data type numeric." appear.
The FLOAT still works, and produces even stranger (but predictable) results of 1000164 and 1000192.
That's the point - if the numbers were coming from an external source (and may have decimal places), you might not have the luxury of knowing the full range of numbers used. As a chemical engineer I learnt about floating point numbers before integers, and converted all measurements to Megafurlongs per microfortnight for consistency (the speed of light is just above 1.8 with those units - far more manageable).
Chris.Strolia-Davis (10/13/2008)
Were the declarations of @SumA and @SumB supposed to confuse us on this one? I found them to be completely unnecessary.
The declarations were not intended to confuse; as a software developer for many decades (mainly with C# for the last 6 years, with some T-SQL on the side), I like to define all my variables of a type that I decide. I don't want to rely on some box of blacklegging binary bits* to make the decision for me. With the latest releases of dotNet allowing implicit declaration types, I'm a bit nervous actually.
Brewmanz
aka Bryan White, NZ
* Thanks to Douglas Adams for this term