How to Interpret Precision and Scale of a Number in a Database

How do I interpret precision and scale of a number in a database?

Numeric precision refers to the maximum number of digits that are present in the number.

ie 1234567.89 has a precision of 9

Numeric scale refers to the maximum number of decimal places

ie 123456.789 has a scale of 3

Thus the maximum allowed value for decimal(5,2) is 999.99

Unable to understand precision and scale of numeric datatype in DBMS

The only database that would not complain and run your code is SQLite, because in SQLite there is no DECIMAL data type.

So, maybe you think that by defining a column as decimal(5, 2) you are defining the column to be numeric with 5 as precision and 2 as scale, but all of them are just ignored.

For SQLite you are just defining a column with NUMERIC affinity and nothing more.

The proper data type that you should have used is REAL and there is no way to define precision and scale (unless you use more complex constraints).

You can find all about SQLite's data types and affinities here: Datatypes In SQLite Version 3

What do scale and precision mean when specifying a decimal field type in Doctrine 2?

Doctrine uses types similar to the SQL types. Decimal happens to be a fixed precision type (unlike floats).

Taken from the MySQL documentation:

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

What is the reason behind precision and scale naming?

Perhaps it's easier if you consider how the numbers look in scientific notation.

X * 10^Y

where X has a single digit before the decimal point.

Now, how big the number is (its "scale") is fundamentally determined by Y. Are we counting in ones? Millions? Thousandths? That's scale.

Regardless of the absolute scale of the number, the digits in X determine how precise we're being. Can I distinguish 1.1 ones from 1.2 ones? Can I distinguish 1.1 millions from 1.2 millions? Can I distinguish 1.1 thousandths from 1.2 thousandths? All are equivalent - two digits (including the one before the decimal point) of precision.

If I can distinguish 1.01 millions from 1.00 millions, that's more precise than only being able to distinguish 0.1 millions; that's 3 digits of precision.

But 1.01*10^-3 is not more precise than 1.01*10^10 ; it merely operates at a smaller scale.

Beyond that, I don't know what you want. Ok, you've told us what you'd like the words to mean; but that's not what they mean. This is what they mean.

UPDATE - One other thing I should mention. It may seem that scale and precision are conflated in some way, because if we take a physical example, surely "1 millimeter from the bullseye" is more precise than "1 meter from the bullseye", right?

But remember that precision and scale describe a variable's data type, not a specific measurement. If measuring in meters, we can't express "1 millimeter from the bullseye" with less than 4 digits of precision ("0.001 meters"); but we could describe "1 meter from the bullseye" with 1 digit of precision. So that actually does align with our desire to call "1 mm from the bullseye" somehow more precise.

What is the default Precision and Scale for a Number in Oracle?

NUMBER (precision, scale)

If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.

A lot more info at:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

Decimal precision and scale

From Precision, Scale, and Length (Transact-SQL)

Looking at the section

Operation:
e1 * e2

you will find

Result precision
p1 + p2 + 1

So in your case 4+4+1 => 9

Further to that, you will notice that

SELECT  SQL_VARIANT_PROPERTY(99.999 * '9.999', 'Precision'), --11 => p1+p1+1
        SQL_VARIANT_PROPERTY(99.999 * '99.999', 'Precision'), --11 => p1+p1+1
        SQL_VARIANT_PROPERTY(99.999 * 9.999, 'Precision'), --10 => p1+p2+1
        SQL_VARIANT_PROPERTY(99.999 * 99.999, 'Precision') -- 11 => p1+p2+1

whereas

SELECT  SQL_VARIANT_PROPERTY(9.999 * '9.999', 'Precision') --9 => p1+p1+1

and

SELECT  SQL_VARIANT_PROPERTY(9.99 * '9.999', 'Precision')

cuases

Arithmetic overflow error converting varchar to data type numeric.

What does precision and scale means in the Oracle NUMBER data type

Check out the online Oracle doc:

p is the precision, or the maximum number of significant decimal
digits, where the most significant digit is the left-most nonzero
digit, and the least significant digit is the right-most known digit.
Oracle guarantees the portability of numbers with precision of up to
20 base-100 digits, which is equivalent to 39 or 40 decimal digits
depending on the position of the decimal point.

s is the scale, or the number of digits from the decimal point to the
least significant digit. The scale can range from -84 to 127.

Positive scale is the number of significant digits to the right of the
decimal point to and including the least significant digit.

Negative scale is the number of significant digits to the left of the
decimal point, to but not including the least significant digit. For
negative scale the least significant digit is on the left side of the
decimal point, because the actual data is rounded to the specified
number of places to the left of the decimal point. For example, a
specification of (10,-2) means to round to hundreds.

How do you access or SELECT Scale and Precision of a Data_Type?

If you are "selecting" the data type, I assume you are "selecting" from something like all_tab_columns. Right? Like this:

select column_name, data_type
from   all_tab_columns
where  owner = 'SCOTT' and table_name = 'EMP'
;

COLUMN_NAME  DATA_TYPE   
------------ ------------
EMPNO        NUMBER      
ENAME        VARCHAR2    
JOB          VARCHAR2    
MGR          NUMBER      
HIREDATE     DATE        
SAL          NUMBER      
COMM         NUMBER      
DEPTNO       NUMBER

Now, you want to know the maximum length for VARCHAR2 columns, and the precision and scale for NUMBER, right?

Add more columns to the select. Like so:

select column_name, data_type, char_col_decl_length, data_precision, data_scale
from   all_tab_columns
where  owner = 'SCOTT' and table_name = 'EMP'
;

COLUMN_NAME  DATA_TYPE    CHAR_COL_DECL_LENGTH DATA_PRECISION DATA_SCALE
------------ ------------ -------------------- -------------- ----------
EMPNO        NUMBER                                         4          0
ENAME        VARCHAR2                       10                          
JOB          VARCHAR2                        9                          
MGR          NUMBER                                         4          0
HIREDATE     DATE                                                       
SAL          NUMBER                                         7          2
COMM         NUMBER                                         7          2
DEPTNO       NUMBER                                         2          0

Alternatively, in SQL*Plus (or other interfaces that implement its functionality) you can use the SQL*Plus command describe:

describe scott.emp

Name     Null? Type         
-------- ----- ------------ 
EMPNO          NUMBER(4)    
ENAME          VARCHAR2(10) 
JOB            VARCHAR2(9)  
MGR            NUMBER(4)    
HIREDATE       DATE         
SAL            NUMBER(7,2)  
COMM           NUMBER(7,2)  
DEPTNO         NUMBER(2)

Very likely, SQL*Plus implements its own describe command with a select query just like the one I shared earlier.