Red Gate forums :: View topic - Floating Point Comparisons incorrectly show differences
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

Floating Point Comparisons incorrectly show differences

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Charles



Joined: 17 Oct 2007
Posts: 13

PostPosted: Thu Oct 25, 2007 7:32 pm    Post subject: Floating Point Comparisons incorrectly show differences Reply with quote

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
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 528
Location: Red Gate Software

PostPosted: Mon Oct 29, 2007 12:56 pm    Post subject: Reply with quote

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
View user's profile Send private message
Charles



Joined: 17 Oct 2007
Posts: 13

PostPosted: Mon Oct 29, 2007 5:43 pm    Post subject: Data Compare Reply with quote

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
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Tue Oct 30, 2007 7:06 pm    Post subject: Reply with quote

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
View user's profile Send private message
kipb7



Joined: 08 Feb 2011
Posts: 13

PostPosted: Thu Sep 15, 2011 11:58 pm    Post subject: Fix Synch before 'sloppy compare' Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group