Sql-server – Moving data from Oracle to SQL Server : Table size optimization

etloraclesql serverssis

I am loading data using SSIS from Oracle to an SQL Server database. One table I moved had a size of 4 MB under Oracle but when loaded to SQL Server, the size grows to 7.96 MB!

I analyzed the size (in KB) depending on the rows inserted and got the following results:

Rows    SQL Server  Oracle
10      16          64
50      48          64
100     88          64
150     128         128
200     168         128
250     208         128
300     248         128
350     288         192
400     328         192
450     368         192
500     408         192
550     448         256
600     488         256
650     528         256
700     568         256
750     648         320
800     688         320

I am suspecting that the datatypes used in SQL Server should be readapted. Here is a summary of the structure of the tables used in Oracle and SQL Server:

Oracle_DataType     SQL_Server_Datatype     #OfColumns
DATE                datetime                2
INTEGER             int                     1
NUMBER(1,0)         numeric(1, 0)           3
NUMBER(15, 5)       numeric(15, 5)          52
NUMBER(2, 0)        numeric(2, 0)           1
NUMBER(3, 0)        numeric(3, 0)           5
NUMBER(3, 1)        numeric(3, 1)           1
NUMBER(5, 0)        numeric(5, 0)           1
NUMBER(5, 2)        numeric(5, 2)           4
NUMBER(6, 0)        numeric(6, 0)           1
NUMBER(8, 3)        numeric(8, 3)           2
NUMBER(8, 4)        numeric(8, 4)           1
NUMBER(9, 0)        numeric(9, 0)           7
NUMBER(9, 3)        numeric(9, 3)           6
VARCHAR2(1)         nvarchar(1)             1
VARCHAR2(12)        nvarchar(12)            1
VARCHAR2(13)        nvarchar(13)            1
VARCHAR2(14)        nvarchar(14)            1
VARCHAR2(16)        nvarchar(16)            3
VARCHAR2(20)        nvarchar(20)            1
VARCHAR2(24)        nvarchar(24)            1
VARCHAR2(80)        nvarchar(80)            1

Do you have any idea if it will be possible to reduce the size in SQL Server so it could, at least, matches the size in Oracle?

Thanks,

Best Answer

Try non unicode strings: varchar instead of nvarchar. Oracle's VARCHAR2 isn't unicode datatype and in nvarchar every character takes 2 bytes instead of 1.