How can we improve SQL Data Compare?

set precision for floating point comparison

[From a customer]
When comparing tables (particularly across 32bit and 64bit machines) floating point numbers specified to say 5 decimal places can differ in their representation in the lower order bits. It would be great to be able to get SQL Data Compare to round the numbers before comparing.

10 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    James MooreJames Moore shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    3 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Anonymous commented  ·   ·  Flag as inappropriate

        Comparing floating point numbers across servers is pointless due to lack of precision inherent in this datatype. DB Compare would be more useful if one could specify the precision (or rounding) for floating point (and why not other?) datatypes.

      • James MooreJames Moore commented  ·   ·  Flag as inappropriate

        There is a workaround here. You can create a view that rounds the floating point numbers before doing the comparison.

        DECLARE @precision INT
        SET @precision = 3

        DECLARE @table TABLE
        (
        [data] FLOAT
        )

        INSERT INTO @table VALUES (15.3444444444442)
        INSERT INTO @table VALUES (15.3444444444443)
        INSERT INTO @table VALUES (15.34442)

        SELECT [data] FROM @table

        -- CREATE View TableView AS
        SELECT ROUND([data], @precision) [data] FROM @table

      Feedback and Knowledge Base