Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

share|improve this question

migrated from stackoverflow.com Sep 28 '12 at 16:24

This question came from our site for professional and enthusiast programmers.

    
What version of SQL Server? If 2008+ use the date datatype there (though this will have only a minor impact on size) –  Martin Smith Sep 28 '12 at 9:45
    
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 –  Amine Chafai Sep 28 '12 at 13:40

2 Answers 2

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.

share|improve this answer
up vote 1 down vote accepted

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:

share|improve this answer
2  
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) –  Martin Smith Sep 28 '12 at 13:42
1  
Here you can read more about 2008+ row compression: blog.sqlauthority.com/2008/07/06/… and msdn.microsoft.com/en-us/library/cc280576.aspx –  Piotr Sobiegraj Sep 28 '12 at 14:22
    
I finaly resolved the issue by using the Row Compression feature. Thanks for your help! –  Amine Chafai Sep 30 '12 at 13:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.