Avg of float inconsistency

by

This is very similiar to: SELECT SUM(...) is non-deterministic when adding the column-values of datatype float.

The problem is that with inaccurate datatype (FLOAT/REAL) the order of of arithmetic operations on floating point matters. Demo from connect:

DECLARE @fl FLOAT = 100000000000000000000
DECLARE @i SMALLINT = 0
WHILE (@i < 100)
BEGIN
    SET @fl = @fl + CONVERT(float, 5000)
    SET @i = @i + 1
END
SET @fl = @fl - 100000000000000000000
SELECT CONVERT(NVARCHAR(40), @fl, 2)
-- 0.000000000000000e+000


DECLARE @fl FLOAT = 0
DECLARE @i SMALLINT = 0
WHILE (@i < 100)
BEGIN
    SET @fl = @fl + CONVERT(float, 5000)
    SET @i = @i + 1
END
SET @fl = @fl + 100000000000000000000
SET @fl = @fl - 100000000000000000000
SELECT @fl
-- 507904

LiveDemo

Possible solutions:

  • CAST all arguments to accurate datatype like DECIMAL/NUMERIC
  • alter table and change FLOAT to DECIMAL
  • you can try to force query optimizer to calculate the sum with the same order.

The good news is that when a stable query result matters to your
application, you can force the order to be the same by preventing
parallelism with OPTION (MAXDOP 1).

It looks like intial link is dead. WebArchive

More Related Contents:

  1. How to check if a column exists in a SQL Server table
  2. Sequence as default value for a column
  3. Joining multiple tables returns NULL value
  4. I dont know sql syntax for my table [closed]
  5. Max Nbr of Months W/ No Activity in Past 12 Months
  6. SQL server query to output the data shown below
  7. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  8. How do I generate a random number for each row in a T-SQL select?
  9. Auto increment primary key in SQL Server Management Studio 2012
  10. Combine multiple results in a subquery into a single comma-separated value
  11. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  12. Floor a date in SQL server
  13. How to drop a table if it exists?
  14. Create a view with ORDER BY clause
  15. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  16. INSERT INTO vs SELECT INTO
  17. Possible to store value of one select column and use it for the next one?
  18. How FOR XML PATH(”) works when concatenating rows
  19. Scheduled run of stored procedure on SQL server
  20. SQL Server ORDER BY date and nulls last
  21. SQL Server variable columns name?
  22. What is the Oracle equivalent of SQL Server’s IsNull() function?
  23. SQL Recursive CTE: Finding objects linked by property
  24. PRINT statement in T-SQL
  25. T-sql – determine if value is integer
  26. Convert decimal time to hours and minutes
  27. how to write number to word function in sql server [closed]
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment