Floating Point Comparisons incorrectly show differences

Charles

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"?

Michelle T

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.

Charles

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

Chris Spencer

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

kipb7

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.