why is decimal data type comparison inconsistent?

esibbald

I am experiencing an inconsistency when comparing tables that differ only by the precision of a column with data type decimal(p,s). In SQL Server terms, the "p" stands for "precision" and the "s" stands for scale, so technically my types only differ in their "scale", but the values therein differ in their decimal precision.

Details: I have two databases with the same table and (roughly) the same data. The tables have some key columns in common, but there is one column with a difference: the "fee" column. In database/table A, this column has type decimal(18,6). In database/table B, this column has type decimal(18,4). The data in the tables is the same with the sole exception of fewer digits after the decimal point in table B because of this column definition difference. If I compare A (as Source) to B (as Target), no differences -- or in some cases very few differences -- show up. If I compare B (as Source) to A (as Target), every value that is missing precision (or, rather, seems to add precision in the Target database) shows as a difference.

Can anyone explain this discrepancy? Can I avoid it somehow? This problem/bug shakes my confidence in the comparison algorithm of this application.

Eddie D

Hi, thank you for your forum post.

What version of SQL Data Compare are you using?  I ask this question as I am unable to replicate the reported problem using V14.0.7.13517.

I created a simple table with one of the columns using  decimal(18,6) and the other DB created the same table using decimal(18,4).

Inserted the same rows of data in both tables.  no matter what DB I select as the source and target and the opposite way around, the rows of data I inserted always exist in both.

Can you please provide some further information and screen shots if possible on what you are experiencing?

Many thanks
Eddie

esibbald

I am using the same version: 14.0.7.13517. I will attempt to replicate the problem with a simpler table layout. I alluded to this in my original question, but didn't expound upon it, but some of the data values are showing as different regardless of the source/target configuration. Most of my data values, however, were not showing different in one configuration, so perhaps it's value-dependent somehow.

Eddie D

Hi, thank you for your reply.

I am unable to replicate the problem.

Are you able to reproduce the error and obtain a screen shot for us to review? 
Also would be possible to obtain a copy of the DDL for each table from the source and target?

Many Thanks
Eddie