Floating Point Comparisons incorrectly show differences

Compares and synchronizes SQL database content.

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

Floating Point Comparisons incorrectly show differences

Postby Charles » Thu Oct 25, 2007 7:32 pm

I'm comparing "identical" tables on 2 servers:

1: SQL Server 2000, 32-bit
2: SQL Server 2005, 64-bit

I find that very often, a column containing floating point numbers will show as different, even though visual inspection of the results shows that it is the same.

I assume that this is a rounding error, not uncommon when working with FP numbers. Perhaps we could add an option to do a "sloppy compare" for floats, e.g. instead of "x=y" it would do something like "abs(x-y) < 0.0000001"?
Charles
 
Posts: 13
Joined: Wed Oct 17, 2007 12:50 am

Postby Michelle Taylor » Mon Oct 29, 2007 12:56 pm

Are you talking about SQL Compare (which compares schema) or SQL Data Compare (which compares data)? SQL Compare doesn't look at the data that a column contains.

SQL Compare does look at column definition elements such as defaults and check constraints, which can contain floating point numbers, but I've just had a look at that and it seems to cope with comparing these on the configurations that you mention.
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Data Compare

Postby Charles » Mon Oct 29, 2007 5:43 pm

Michelle Taylor wrote:Are you talking about SQL Compare (which compares schema) or SQL Data Compare (which compares data)?

SQL Data Compare.

For example, our database uses a lot of fields defined as FLOAT(), which only use a total of 32 bits for storage -- 24 bits for the mantissa -- providing only 7 decimal digits of guaranteed precision. Apparently, there are some slight differences in the "rounding" between the two platforms, whether in the FP processor or software, such that the numbers often differ past that. I'd say that SQL Server is converting them on output to a greater precision.

For example, the numbers
109.06
102.81

are displayed by SQL Data Compare as:

109.05999755859375 (32-bit SQL2000)
109.05999755859381 (64-bit SQL2005)

102.80999755859375 (32-bit SQL2000)
102.80999755859381 (64-bit SQL2005)

Knowing that the precision of these numbers is limited to 7 significant digits, they might be rounded and compared as:
102.8100
102.8100
Charles
 
Posts: 13
Joined: Wed Oct 17, 2007 12:50 am

Postby Chris Spencer » Tue Oct 30, 2007 7:06 pm

Issues such as this do occasionally come up so your idea of a 'sloppy compare' option is a good one and one that we will consider adding in future versions.

Thanks again for the excellent feedback.

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Fix Synch before 'sloppy compare'

Postby kipb7 » Thu Sep 15, 2011 11:58 pm

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.
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