March 27, 2017 at 6:00 am
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 from performance point of view?
If not, exactly why would it hamper performance, even if actual data is integer?
Regards,
Kaushik
March 27, 2017 at 6:37 am
I never implement weird things so I don't know empirically the performance impact. Float(53) is eight bytes so it is equal to a bigint data type. My main concern would be that float is an imprecise data type so there are some numbers that get rounded in the process of being stored. Will this cause duplicates for you when rounding two different numbers? As for performance it would take a bit longer to interpret a mantissa and exponent rather than a simple bigint. I don't know if indexes "interpret" exponents but they might need to in order to determine sorting.
Can't you CAST the column to an int and store it in 4 bytes?
March 27, 2017 at 10:43 am
Check for a way to override the data type to be bigint (or even int, if you know that will hold all the values) instead of float(53).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 27, 2017 at 1:41 pm
It is advisable that you identify the correct mapping for the oracle types and apply those instead.
Oracle is has its own way of dealing with datatypes and that way is not necesserally the correct way on SQL Server.
For example DATE on Oracle always contains the time part.
When migrating to SQL Server an exercise should always be done to see if the Oracle data can contain anything other than a date part (as this may be restricted by the application) and the appropriate data type used on SQL Server
Same goes with NUMBER
if you look at the definition on the oracle side by querying the metadata tables directly you will see that a number can have several types of definitions, and not all of them are always what they seem.
For example a number defined as
DATA_PRECISION = 38
DATA_SCALE = null or * (not sure now)
can either be a Int or a decimal without fixed decimal places
or
For example a number defined as
DATA_PRECISION = 8
DATA_SCALE = 0
Although the above could be indeed mapped as a numeric (8, 0) to SQL Server it is more likely that it should be mapped as a INT
So my advice is to look at the definition - then look at how the application uses that data and what really it does expect to be in each column - then look at the data itself - sometimes the 2 previous steps point to one particular usage, and the data says otherwise.
Also codepage - have you confirme that the data on your oracle tables is indeed matching the table codepage definition? I have seen way too many times the database to be configured as WE8ISO8859P1 and the data on the tables being WE8ISO8859P15 - this leads to bad conversion when moving to SQL Server if work arounds aren't applyed.
This is often the case with legacy databases that are for example written to by COBOL programs.
https://www.mssqltips.com/sqlservertip/2944/comparing-sql-server-and-oracle-datatypes/ is a good link to read
March 28, 2017 at 11:07 am
The direct answer is NO. Float (53) is not 8 bytes and imprecise as has already been mentioned. Another problem is that, although, your existing data is int, using float would allow for decimal and negative numbers.
Int (4 bytes) is ideal, bigint (8 bytes) if int is too small for your needs as Scott said.
-- Itzik Ben-Gan 2001
March 28, 2017 at 12:42 pm
I meant float (53) IS 8 bytes (...and can't figure out how to edit using the new forum format.)
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply