Search code examples
sql-serverinformaticasqldatatypes

What should I keep as datatype in Informatica for a field that is FLOAT in source and target?

I have a field with FLOAT(53) datatype in SQL Server. Upon importing it in Informatica, it becomes FLOAT(15).

What datatype I should be using to get max possible accuracy?

Solution

  • Ok, a bunch of issues here.

    1. Check this doc to learn about the available data types in Informatica
    2. If you want "max possible accuracy" you should use DECIMAL :) FLOAT (even Double) - is not a precise data type. It allows large numbers, it allows fractions - but it's not precise. In fact it's impossible to represent 0.1! :)
    3. If you'll use DECIMAL, you'll have the precise number as long as it won't exceed 15 digits. For a larger numbers you will end up with DOUBLE anyway, unless...
    4. You enable HIGH PRECISION in your session properties. Then you will keep using precise DECIMAL numbers up to 38 digits.
    5. If you'll exceed 38 digits, you will end up with non-precise DOUBLE anyway. Yup - even with HIGH PRECISION enabled - check this doc.

    In your case I presume you should use DOUBLE - the FLOAT in your SQL Server is not a precise type anyway.

    This is also a great summary on the issue.