SELECT SUM(...) is non-deterministic when adding the column-values of datatype float | Microsoft Connect

For those of you using float expecting precise decimal values from SQL Server when performing aggregate functions or multiplication/division, don’t. 

http://en.wikipedia.org/wiki/Fixed-point_arithmetic

http://en.wikipedia.org/wiki/Computer_numbering_formats

One recommendation for storing monetary values is to use the Money data type. 

http://sqlcat.com/technicalnotes/archive/2008/09/25/the-many-benefits-of-money-data-type.aspx

Apparently this helps with performance during Analysis Services data loads, if currency type is selected in Analysis Services to match the data types.  (getting rid of implicit conversion to double)

However, if you need to do any kind of basic math or rounding, money is not a good fit.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a544f732b84257dd/8926f7fa31fa4832?hl=en&lnk=st&q&pli=1

Use decimal with the precision you need to get a correct answer, or use float and always round your results.

http://www.sqlservercentral.com/Forums/Topic544518-9-1.aspx

Note that PerformancePoint Planning and cube writeback uses float data type, which can cause rounding and comparison issues. 

SELECT SUM(...) is non-deterministic when adding the column-values of datatype float | Microsoft Connect