- Code: Select all
`DECLARE @fa FLOAT = 1.0646328852929699e-2`

DECLARE @fb FLOAT = 1.0646328852929700e-2

SELECT 'fa'[number], @fa [f], CONVERT(DECIMAL(25,20), @fa) [decimal] UNION

SELECT 'fb'[number], @fb [f], CONVERT(DECIMAL(25,20), @fb) [decimal] UNION

SELECT 'diff', (@fa-@fb), CONVERT(DECIMAL(25,20), @fa-@fb)

--Output:

number f decimal

fa 0.0106463288529297 0.01064632885292970000

fb 0.0106463288529297 0.01064632885292970000

diff -1.73472347597681E-18 0.00000000000000000000

These two floats look identical if you use SQL Server 2008 R2's conversion to decimal, but they are not identical.

While a float might approximate a particular real number, it is an exact binary value representing an exact number.

It is reasonable to expect operations on it to give the closest answer and not resort to "but it's approximate!"

Here the question is whether the decimal representation is the best available for reproducing that float when doing synchronization.

SQL Server fails at this task.

SQL Data Compare does not have to fail if it would use 17 significant decimal digits or transfer numbers in a different form.

This problem is in addition to the earlier reported need for SQL Data Compare to specify that the data value is float by using E0 on the end, since SQL otherwise assumes it is decimal and incorrectly converts some numbers to float, such as 0.85195833333333271 versus 0.85195833333333271e0 which are not equal numbers to SQL.