Re: Slightly OT: Size of Oracle vs MS SQL database tables

Michael L. Squires wrote:

> We've moved one database over to MS SQL Server 2005 and have discovered that
> all of our data tables more than double in size under MS SQL Server 2005.
> The net effect is to more than double the amount of disk space required to
> implement the same database.
>
> A search of various sources of MS information haven't turned up a magic
> switch to reduce the size of these tables.
>
> Any pointer to some information about this difference, expecially if there's
> something obvious that we're missing, would be greatly appreciated.
How have data types been mapped?
In Oracle numeric values are typically stored as NUMBER, very often without defining a scale/precision because small numbers take little space in Oracle (like VARCHAR).
If you map every column that has numbers between 1 and 10 to an 8 byte floating point your database will see a lot of growth. Now SQL Server 2005 AFAIK has introduced a var-decimal kind of type in a service pack. Maybe that's all you need to match. Otherwise you should take a look at the data and exploit SQL Server native types as appropriate. E.g. a tinyint will beat any NUMBER in footprint.
Similar things are true for DATE. In Oracle DATE includes DATE and TIME. I'm not familiar if SQL Server has a DATETIME data type. But If you get a mapping to TIMESTAMP (which includes microseconds) you will loose out.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab