| Author |
Message |
phispher
Joined: 22 Nov 2005
Posts: 7
|
 Posted: Wed Nov 08, 2006 12:27 am Post subject: FLOAT issue with SQL Data Compare 5 |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6348
Location: Red Gate Software
|
| Posted: Wed Nov 08, 2006 3:48 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
phispher
Joined: 22 Nov 2005
Posts: 7
|
| Posted: Wed Nov 08, 2006 7:54 pm Post subject: |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6348
Location: Red Gate Software
|
| Posted: Fri Nov 10, 2006 2:59 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
kipb7
Joined: 08 Feb 2011
Posts: 13
|
| Posted: Thu Sep 15, 2011 1:30 am Post subject: A Float not expressible in decimal - need 17 sig figs |
|
|
| 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 |
|
|
|
