none
SQL Server primary key on float data type RRS feed

  • Question

  • Hi,

    I am doing migration from Oracle to SQL Server. The tool I am using for migration (SQL Server Migration Assistant) automatically converts Oracle's 'number' data type to SQL Server's 'float(53)'. 

    Is it advisable to use 'float(53)' as primary key, assuming it would contain only integer values? 

    Other option which I have is to use 'numeric(19,0)' which occupies 9 bytes compared to 8 bytes for 'float(53)'. Which would be better data type for primary key?


    Regards,

    Kaushik

    Monday, March 27, 2017 11:39 AM

All replies

  • It is best to use the int data type. Float offers better storage than numeric.
    Monday, March 27, 2017 11:43 AM
  • The migration wizard is extremely simplistic.  You should not leave those datatypes as float. You should reassign them to valid data types related to your data.
    Monday, March 27, 2017 12:07 PM
  • In SSMA you can define a customer data type mapping to automatically map numeric to integer: Mapping Oracle and SQL Server Data Types (OracleToSQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 27, 2017 12:20 PM
  • Hi,

    What kind of values do you have at the source ?

    Are they integers, floats or fixed precision decimals ?

    In my opinion, your SQL destination should match the source as much as possible

    Monday, March 27, 2017 12:32 PM
  • Thanks all for your replies...the problem is that it would affect all columns of type number. For some columns, it may not be a primary key and may contain float values also.
    Monday, March 27, 2017 12:46 PM
  • The ones with primary keys are integers only.

    Monday, March 27, 2017 12:47 PM
  • Hi,

    OK, if your primary keys are integers choose integers as SQL Server destination.

    In SQL Server you have tinyint (1 byte), smallint (2 bytes), int (4 bytes) and bigint (8 bytes) signed integers.

    For anything else you can choose numeric(x,0) fields which give you up to 38 digits integers

    Monday, March 27, 2017 1:04 PM