Red Gate forums :: View topic - FLOAT issue with SQL Data Compare 5
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

FLOAT issue with SQL Data Compare 5

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
phispher



Joined: 22 Nov 2005
Posts: 7

PostPosted: Wed Nov 08, 2006 12:27 am    Post subject: FLOAT issue with SQL Data Compare 5 Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6647

PostPosted: Wed Nov 08, 2006 3:48 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
phispher



Joined: 22 Nov 2005
Posts: 7

PostPosted: Wed Nov 08, 2006 7:54 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6647

PostPosted: Fri Nov 10, 2006 2:59 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
kipb7



Joined: 08 Feb 2011
Posts: 13

PostPosted: Thu Sep 15, 2011 1:30 am    Post subject: A Float not expressible in decimal - need 17 sig figs Reply with quote

Code:
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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group