FLOAT issue with SQL Data Compare 5

phispher

I have searched in vain for a resolution to this problem. Let me set the stage first. I have a table in SQL with a float column. I used Query Analyzer to query a value and I get:

-97.398719999999997

However, when SQL Data Compare attempts to synchronize this to another database, it tries to pull it accross as follows:

-97.39872

Since we have a unique contraint on that column, this obviously causes issues. How can I resolve the problem.

Thanks for you help in advance.

Brian Donahue

Hi Jason,

If this is the same issue, we have seen this before in an older version of Data Compare. It's not the accuracy of the software that's to blame, but rather what happens to the data as it appears in the results grid. Since all float numbers are rounded off, the values can potentially be different, even if the byte values are equal. If you produce a migration script from this data comparison, is the value reflected correctly in the script?

Thanks!

phispher

Here is a line i pulled from the sync script:

INSERT INTO [dbo].[DATA_FLOAT] ([data_float_id], [data_float], [_temp_data_float_id]) VALUES (25555, -97.39872, NULL)

So, as you can see, the sync script is truncating it too.

Brian Donahue

Hello,

Sorry, I think I've given out some wrong information. It's actually SQL Server performing the rounding. This is true with float numbers, which rather than being hard values, are approximations.

kipb7

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.