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)

You must be logged in to reply to this topic. Login to reply