Numeric(p,s) datatype in TSQL

• 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

here is an example,

-- drop table TestdataType

create table TestdataType

(

col1 numeric(5,5)

)

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.

• 21.3333 can't fit into a (5,5) numeric. 21.3333 has 2 numbers to the left of the decimal, but (5,5) indicates that all the numbers will be to the right of the decimal. (7,5) would leave you 2 to the left and 5 to the right, so 21.3333 would fit.

And then again, I might be wrong ...
David Webb

• When you have a numeric, the first number (p) is the total number of digits that can be stored. The second number (s) is how many of those digits are on the right of the decimal point. Numeric(5,5) means store 5 digits total, all 5 on the right of the decimal point.

DECLARE @test-2 Numeric(5,5)

set @test-2 = 0.12345

select @test-2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
• Thank you David & Gail

Viewing 4 posts - 1 through 3 (of 3 total)