Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Numeric(p,s) datatype in TSQL Expand / Collapse
Author
Message
Posted Tuesday, April 26, 2011 10:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 2:18 PM
Points: 248, Visits: 172
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
(
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.
Post #1098780
Posted Tuesday, April 26, 2011 10:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:09 PM
Points: 901, Visits: 7,180
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
Post #1098807
Posted Tuesday, April 26, 2011 10:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 Numeric(5,5)
set @test = 0.12345
select @test



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1098810
Posted Tuesday, April 26, 2011 10:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 2:18 PM
Points: 248, Visits: 172
Thank you David & Gail
Post #1098816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse