why is decimal data type comparison inconsistent?
esibbald
Posts: 2 New member
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.
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.
Tagged:
Answers
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com