Numeric datatype in TSQL: http://msdn.microsoft.com/en-us/library/ms187746.aspx
According to the definition and article in the link above, Scale in a numeric datatype is 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.
My problem is that I am not able to insert records into a column with numeric datatype where scale is equal to precision i.e (s = p), on the other hand, I am able to insert records into the column only if [s < (p-2)]
here is an example,
-- drop table TestdataType
create table TestdataType
insert into Testdatatype (col1) values (21.3333) -- does not work, arithmetic exception error
but if you alter the column datatype to numeric(5,3) or to numeric(7,5), instead of numeric(5,5) the insert works. It will not work even when it is converted to numeric(5,4) or to numeric(6,5), can someone explain the anomaly
Here is the exception message in full
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
FYI: I am using SQL Server 2005 SP3 Dev edition.