Floating Point Comparisons incorrectly show differences

CharlesCharles Posts: 15
edited September 15, 2011 6:58PM in SQL Data Compare Previous Versions
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"?

Comments

  • 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.
    Software Developer
    Redgate Software
  • 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
  • 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 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.
Sign In or Register to comment.