CODEWITHSUNDEEP
sql-server
Richard Barraclough
Richard Barraclough

Reputation: 2964

In SQL Server, why is 12538335.3 not decimal(10, 4)?

In SQL Server, why is 12538335.3 not decimal(10, 4)? Nor is it decimal(11, 4) but it is decimal(12, 4).

CREATE TABLE T (d decimal(10, 4) NOT NULL)

INSERT INTO T (d) 
VALUES (12538335.3)
-- Arithmetic overflow error converting numeric to data type numeric.

ALTER TABLE T ALTER COLUMN d decimal(11, 4)

INSERT INTO T (d) 
VALUES (12538335.3)
-- Arithmetic overflow error converting numeric to data type numeric.

ALTER TABLE T ALTER COLUMN d decimal(12, 4)

INSERT INTO T (d) 
VALUES (12538335.3)
-- OK

Upvotes: 1

Views: 6631

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

[ 1 ]

[...]why is 12538335.3 not decimal(10, 4)? Nor is it decimal(11, 4) but it is decimal(12, 4).

Well, according to SQL_VARIANT_PROPERTY it's NUMERIC(9, 1):

SELECT SQL_VARIANT_PROPERTY(12538335.3, 'BaseType') AS DataType,
    SQL_VARIANT_PROPERTY(12538335.3, 'Precision') AS [Precision],
    SQL_VARIANT_PROPERTY(12538335.3, 'Scale') AS [Scale]
/*
DataType Precision Scale
-------- --------- -----
numeric  9         1    
*/

where

  • 9 = total number of digits (left side of decimal separator + right side)
  • 1 = number of digits after separator.

For this data type, maximum value is 999,999,999.9

[ 2 ] Following piece of code

CREATE TABLE T (d decimal(10, 4) NOT NULL)

INSERT INTO T (d) 
VALUES (12538335.3)

is trying to insert 12538335.3 / NUMERIC(9,1) (8digits . 1digit) into a NUMERIC(10,4) (6digits . 4digits). Because target data type / NUMERIC(10,4) has only 6digits for the left side of decimal separator (compared with 8 digits required for 12538335.3) above INSERT statement generates an exception:

Arithmetic overflow error converting numeric to data type numeric.

that basically means 

Arithmetic overflow error converting numeric(9,1) to data type numeric(10,4).

Upvotes: 0

S3S
S3S

Reputation: 25112

Precision and Scale...

12,4 is 12 total digits with 4 to the right of the decimal. 12538335.3 is 9 digits... but when you account for the extra 0's for the scale defined in decimal(12,4) you get 12538335.3000 which is 12 total digits. Thus, this scenario works.

11.4 doesn't work, because 11 (precision) digit's isn't enough to account for the extra 0's on the end for the scale you have declared (4).

This is the same for 10,4

declare @var decimal (12,4)
set @var = 12538335.3
select @var 
--returns
12538335.3000

Upvotes: 3

Richard Barraclough
Richard Barraclough

Reputation: 2964

The misunderstanding arises from the phrase total number of digits.

It means the total number of digits after the number is padded with zeros on the right to fill the scale.

Upvotes: 1

Related Questions