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 ofnvarchar
. Oracle'sVARCHAR2
isn't unicode datatype and innvarchar
every character takes 2 bytes instead of 1.