Database field definitions

Question

Hi,

I need to do a data migration from a data base and I'm not too familiar with databases so I would like some clarification. I have some documentation that could apply to either an Oracle or a SQL database and it has a column defined as NUMBER(10,5). I would like to know what this means. I think it means that the number has 10 digits with 5 after the decimal point, but I would like clarification. Also would this be different for either SQL or Oracle?

Answer 1

The first number is precision the second number is scale. The equivalent in SQL Server can be as Decimal / Numeric and you could define it like so:

DECLARE @MyDec decimal(18,2)

The 18 is the max total number of decimal digits that can be stored (that is the total number of digits, for instance 123.45 the precision here is 5, while the scale is 2). The 2 is the scale and it specifies the max number of digits stored to the right of the decimal point.

See this article

Just remember the more precision the more size in storage bytes. So keep it at a minimum if possible.

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Finally, it is worth mentioning that in oracle you can define a scale greater then a precision, for instance Number(3, 10) is valid in oracle. SQL Server on the other hand requires that the precision >= scale. So if you defined Number(3,10) in oracle, it would map into sql as Number(10,10).

Answer 2

In Oracle, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point.

From the Oracle documentation

NUMBER(p,s)

where: p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38. s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

Here are some examples :

Actual data .000127 stored in NUMBER(4,5) becomes .00013

Actual data 7456123.89 stored in NUMBER(7,-2) becomes 7456100

Edited

JonH mentions something noteworthy:

Oracle allows the scale > precision, so SQL will map that so that if s>p then p becomes s. That is NUMBER(3, 4) in oracle becomes NUMERIC(4,4) in SQL.

Answer 3

Defining a column in Oracle as NUMBER(10,5) means that the column value can have a decimal of up to five places of precision, and ten digits in overall length. If you insert a value into the column that does not have any decimal places defined, the maximum the column will support is 10 digits. For example, these values will be supported by the column defined as NUMBER(10,5):

1234567890
 12345.67890

It made validation a pain.

MySQL and SQL Server don't support the NUMBER data type - to support decimals, you're looking at using DECIMAL (or FLOAT?). I haven't looked at PostgreSQL, but I would figure it to be similar to Oracle.