Decimal vs. int when using ORMs

Question

I have been using ORM tools/code generators and I see that they prefer using decimal to int values when mapping columns to properties. Is there any advantage of using decimal ?

The column type in database is the default Number which created as Number(38, 0) I believe.

Coachman

What is the exact type of the original database column? In my experience, they prefer int, but only when that actually matches what needs to be stored. – Crosslegged

@MarcGravell Number (Oracle) – Esma

NUMBER(what,what) ? – Crosslegged

@MarcGravell It is just Number, I don't specify anything about precision and scale – Esma

?!? NHibernate uses int by default, this must be an oracle / llblgen thing. It's definitely not NH. – Rosaliarosalie

@Rosaliarosalie I am using codeplex.com/Wikipage?ProjectName=nmg for Fluent NHibernate and it's generating properties with decimal type. – Esma

That's not NH, that's a 3rd party tool. I have no idea what that tool is, because it's not hard to write that code yourself. The screenshots on the site show SQL Server generating int properties. – Rosaliarosalie

Answer 1

NUMBER is NUMBER(38), which has far larger possible range than int (Int32) (and much larger than long, too). double has different rounding semantics, so decimal is preferred to avoid errors and issues. If the ORM can guarantee that the data fits inside int, it might be happier to use int.

Crosslegged

Thanks that makes sense and btw that was a really fast-answer – Esma

Answer 2

Decimal is a 128-bit datatype. An Int32 is 32-bit, and is too small for general purpose, because tables commonly have row counts that overflow a 32-bit int. Some tools just default to a NUMBER(38) or the INTEGER alias in Oracle, which maps to the same, and some tools take the easy way out and use Decimal, while others try to map more closely to the corresponding value ranges.

Considering how large an Oracle NUMBER(38) can be, (38 significant digits is a large number), Decimal is the only safe option. But if you know you are storing sequential values, then an Int64 is practical enough, because even Decimal could potentially overflow with an Oracle NUMBER. Decimal can hold up to 79,228,162,514,264,337,593,543,950,335. That is "only" 29 significant digits and still can't hold a NUMBER(38) max value.

If you want a closer mapping, you need to use smaller precision NUMBER fields in Oracle. I use:

NUMBER(9) => Int32
NUMBER(18) => Int64
NUMBER(19+) => Decimal

in the data access code generators that I've written. ORMs may do the same, or not. Typically NUMBER(18) is adequate for any integer keys you will ever need.

If you aren't doing arithmetic with your primary keys, then I can't imagine any dis-advantage to using a Decimal type if you just want the "fire and forget" and never worry about a value that doesn't fit. In OLTP systems there is neglible performance difference between using a Decimal and an Int64, and Oracle doesn't care whether you define a field as NUMBER(1) or NUMBER(38) as far as data store, the NUMBER() type is a variable length type like VARCHAR and will only take up as much space as is needed for the particular value in each row. It is not a fixed length storage so in effect you are only constraining the potential value, not saving space.

SQL> insert into bbb values(1);

1 row created.

SQL> insert into bbb values(11111111);

1 row created.

SQL> insert into bbb values(1111111111111111111111111);

1 row created.

SQL> select i, vsize(i) from bbb;

         I   VSIZE(I)
---------- ----------
         1          2
  11111111          5
1.1111E+24         14

Alper