April 26, 2011 at 10:18 am
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.
April 26, 2011 at 10:43 am
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.
April 26, 2011 at 10:48 am
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
April 26, 2011 at 10:52 am
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