Moving data from Oracle to SQL Server : Table size optimization

Question

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,

• Martin Smith: What version of SQL Server? If 2008+ use the date datatype there (though this will have only a minor impact on size)
• Amine Chafai: I use SQL Server 2008 R2 Enterprise Edition, it's true that the impact of date datatype will be negligeable since there is only 2 columns using it

Answer 1

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.

Answer 2

Thanks Piotr for your suggestion. I find what really causes such big divergence between Oracle and SQL Server! It's due to NUMBER / NUMERIC data type (notice that 85 columns use NUMBERC/NUMBER datatype) !

While the storage size of a NUMBER(p,s) data vary under Oracle depending on the value of stored number, the size of NUMERIC(p,s) number still constant on SQL Server, whatever the value is!

For Instance:

• Under Oracle NUMBER(5,2) column we store the following values : (123.45, 12.23 and 1) the storage space is 4 bytes, 3 bytes and 1 bytes respectively, what sum up to 8 bytes
• However, under SQL Server NUMERIC(5,2) column the storage required for all the three values is 5 bytes what sum up to 15 bytes... (almoste twice the space under Oracle)

For more details check these two resources:

• decimal and numeric (Transact-SQL) from Books Online
• Size of NUMBER(for Oracle)

• Martin Smith: There is a vardecimal storage format in SQL Server enterprise edition BTW but that is deprecated in favour of more general page / row compression now (you say you are using Enterprise Edition so you can use this)
• Piotr Sobiegraj: Here you can read more about 2008+ row compression: blog.sqlauthority.com/2008/07/06/… and msdn.microsoft.com/en-us/library/cc280576.aspx
• Amine Chafai: I finaly resolved the issue by using the Row Compression feature. Thanks for your help!