Comments
Sort by recent activity
Chris wrote, "Issues such as this do occasionally come up so your idea of a 'sloppy compare' option [for floats] is a good one and one that we will consider adding in future versions."
I know this is an old thread, but if anyone is still considering this, I'd encourage you to FIRST implement accurate synchronization of floats, so there is less need for sloppy compare.
An example is the float 1.0646328852929699e-2 which SQL Data Compare does not express with enough decimal places to make a valid copy. / comments
Chris wrote, "Issues such as this do occasionally come up so your idea of a 'sloppy compare' option [for floats] is a good one and one that we will consider adding in future versions."
I know this ...
ronnymr wrote,
"I am comparing 1 column of data (varchar 255) and getting unexpected results. Although many rows match, there are instances where rows do not match, yet the data appears identical. I have tried querying both database tables with the suspect data and I get matches in both. "
If your varchar has a trailing space in one version and not in the other, SQL Data Compare will say they are different, but they will look alike.
You can also do the SELECT * FROM xxx WHERE vchar='hello'
and get the row from each one -- even though one of them has vchar='hello ' (with trailing space)
even doing len(vchar) will give the space-trimmed length, so you won't see the difference.
One way to see the difference is to do
select vchar+'x' [vcharx] from xxx where vchar='hello' / comments
ronnymr wrote,
"I am comparing 1 column of data (varchar 255) and getting unexpected results. Although many rows match, there are instances where rows do not match, yet the data appears identical...
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. / comments
DECLARE @fa FLOAT = 1.0646328852929699e-2
DECLARE @fb FLOAT = 1.0646328852929700e-2
SELECT 'fa'[number], @fa [f], CONVERT(DECIMAL(25,20), @fa) [decimal] UNI...
I still get this error in SQL Data Compare 9.0.0.117
In the past, I've just created a new project. It is tiresome to re-discover the various settings I had set for comparing certain database pairs.
Perhaps the product could give a more descriptive message, such as which table or column, or what we can do about it? / comments
I still get this error in SQL Data Compare 9.0.0.117
In the past, I've just created a new project. It is tiresome to re-discover the various settings I had set for comparing certain database pairs...
As we've seen earlier, many floats are not correctly synchronized by SQL Data Compare.
Previously, it was suggested that appending e0 onto the end of a float would make SQL process it as a float instead of as a decimal. Here is a case where that does not work, and it may be an error in how Red Gate generates the decimal value for a float.
create table dbo.FloatTest (PK INT PRIMARY key, Fraction FLOAT)
insert into dbo.FloatTest (PK, Fraction) Values (1, 0.07689223240363979e0)
DECLARE @F1 FLOAT
SELECT @F1 = Fraction FROM dbo.FloatTest
PRINT CONVERT(DECIMAL(17,17),@f1)
--prints 0.07689223240363979 -- looks good, right?
Also create this table on another database - but without the INSERT.
Now use SQL Data Compare and see what value is generated:
INSERT INTO [dbo].[FloatTest] ([PK], [Fraction]) VALUES (1, 0.0768922324036398)
--This rounds to 16 places, it seems, or 15 significant figures.
As in the original report, this is the incorrect binary value for the float. Here's the new part: adding on e0 is ALSO INCORRECT:
INSERT INTO [dbo].[FloatTest] ([PK], [Fraction]) VALUES (1, 0.0768922324036398e0)
So, would it be possible to get Red Gate Data Compare's Synchronize to synchronize floats correctly? We have been manually appending e0 onto floats for some months now when synchronizing. Now we see this is not enough. / comments
As we've seen earlier, many floats are not correctly synchronized by SQL Data Compare.
Previously, it was suggested that appending e0 onto the end of a float would make SQL process it as a float in...
You might enjoy the Microsoft response on this; It perhaps indicates that Red Gate should represent floats with E notation, not default decimal. Here is my report to Microsoft:
--Some floats parse inconsistently:
DECLARE @A FLOAT, @B FLOAT
SET @A = 0.8519583333333327
SET @B = 0.85195833333333271
IF @A>@B PRINT 'Greater' --prints
PRINT @A-@B --positive (expect 0 or negative)
One can work around this either by appending 'E0' onto the end of the float or by using CONVERT(float,'0.85195833333333271'). The numbers above are equal using either. These also helped with about 10,000 other such cases. Here is the reply:
"The behavior you are seeing is by design. When you specify a floating point value without the scientific notation, it is actually considered a decimal value not float. And you are then converting the decimal value to float which can result in rounding errors depending on the value.
"In your case, the value for @A is represented by decimal(16, 16) and @B is represented by decimal(17, 17). As you discovered, you
should use the scientific notation for specifying the floating point value.
"Btw, you can determine the type of a constant by doing something like below:
declare @v sql_variant = 0.8519583333333327;
select sql_variant_property(@v, 'BaseType') ,
sql_variant_property(@v,'Precision') ,
sql_variant_property(@v, 'Scale');
-- Umachandar, SQL Programmability Team" / comments
You might enjoy the Microsoft response on this; It perhaps indicates that Red Gate should represent floats with E notation, not default decimal.Here is my report to Microsoft:
--Some floats parse ...
There is incorrect Red Gate synchronization of floats between databases, caused by the conversion to string and back to floats via the script that SQL Data Compare creates, and a bug in SQL Server:
SQL's parsing of floats is buggy: DECLARE @f1 FLOAT, @f2 FLOAT
SET @f1 = 0.8519583333333327
SET @f2 = 0.85195833333333271
IF @f1>@f2 PRINT 'Greater' --True - prints
(Tested in SQL 2008 and SQL 2005)
SQL Data Compare synch scripts could work around this defect by changing UPDATE TABLEX SET [Val]=0.85 WHERE [KEYX]=3
to UPDATE TABLEX SET [Val]=0.85E0 WHERE [KEYX]=3
The E0 worked in all 10,000 cases I had where SQL Data Compare did not correctly synchronize, none of which had E in the float already.
Having SQL Data Compare Synchronization so it actually synchronizes would save me a lot of time in working around this. / comments
There is incorrect Red Gate synchronization of floats between databases, caused by the conversion to string and back to floats via the script that SQL Data Compare creates, and a bug in SQL Server:...
Thanks for the super-fast reply!
Now I wonder how the SQL Server value is converted to string to use in the script. In the programming language Python, they have two ways to show a string from a float - human readable (str) and suitable for re-creating the original value (repr).
Perhaps the Red Gate "float-to-string" logic is not quite right for this application -- there is another "decimal" representation of a given float that, when converted to binary, will give the original binary value.
Do a Google search for: burger indiana.edu float
"Printing Floating-Point Numbers Quickly and Accurately"
It is the 'Accurately' that may be the issue here.
Thanks for the help! / comments
Thanks for the super-fast reply!
Now I wonder how the SQL Server value is converted to string to use in the script. In the programming language Python, they have two ways to show a string from a f...
I did the SSMS > SQL Prompt 5 > Help > Check for Updates ...
and it says I have the latest version 5.1.8.2, and I unchecked
"Check for updates on startup"
but it still takes 30 seconds to start up SSMS.
One must use a proxy on our network to get outside, so that may interfere with phoning home. / comments
I did the SSMS > SQL Prompt 5 > Help > Check for Updates ...
and it says I have the latest version 5.1.8.2, and I unchecked
"Check for updates on startup"
but it still takes 30 seconds to start up ...