| Author |
Message |
Charles
Joined: 17 Oct 2007
Posts: 13
|
 Posted: Thu Oct 25, 2007 7:32 pm Post subject: Floating Point Comparisons incorrectly show differences |
 |
|
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"? |
|
| Back to top |
|
 |
Michelle Taylor
Joined: 30 Oct 2006
Posts: 519
Location: Red Gate Software
|
| Posted: Mon Oct 29, 2007 12:56 pm Post subject: |
|
|
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. |
|
| Back to top |
|
|
Charles
Joined: 17 Oct 2007
Posts: 13
|
| Posted: Mon Oct 29, 2007 5:43 pm Post subject: Data Compare |
|
|
| 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 |
|
| Back to top |
|
|
Chris Spencer
Joined: 29 Aug 2006
Posts: 300
Location: Red Gate - Cambridge
|
| Posted: Tue Oct 30, 2007 7:06 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
kipb7
Joined: 08 Feb 2011
Posts: 13
|
| Posted: Thu Sep 15, 2011 11:58 pm Post subject: Fix Synch before 'sloppy compare' |
|
|
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. |
|
| Back to top |
|
|
|
