FLOAT issue with SQL Data Compare 5

Compares and synchronizes SQL database content.

Moderators: Chris Auckland, David Atkinson, richardjm, david connell

FLOAT issue with SQL Data Compare 5

Postby phispher » Wed Nov 08, 2006 12:27 am

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.
phispher
 
Posts: 7
Joined: Tue Nov 22, 2005 5:47 pm

Postby Brian Donahue » Wed Nov 08, 2006 3:48 pm

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!
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby phispher » Wed Nov 08, 2006 7:54 pm

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.
phispher
 
Posts: 7
Joined: Tue Nov 22, 2005 5:47 pm

Postby Brian Donahue » Fri Nov 10, 2006 2:59 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

A Float not expressible in decimal - need 17 sig figs

Postby kipb7 » Thu Sep 15, 2011 1:30 am

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.
kipb7
 
Posts: 13
Joined: Tue Feb 08, 2011 12:43 am


Return to SQL Data Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests