Why SQL Server throws Arithmetic overflow error converting int to data type numeric?

Question

Hi guys

I have an error being thrown by SQL Server Management Studio when running this code:

declare @percentage numeric(3,2)
set @percentage = cast(15 as numeric(3,2))

but when I change numeric declaration to

declare @percentage numeric(4,2)
set @percentage = cast(15 as numeric(4,2))

everything goes fine.

Is there a limitation for numeric data type?

Answer 1

Numeric defines the TOTAL number of digits, and then the number after the decimal.

A numeric(3,2) can only hold up to 9.99.

Answer 2

Lets see, numeric (3,2). That means you have 3 places for data and two of them are to the right of the decimal leaving only one to the left of the decimal. 15 has two places to the left of the decimal. BTW if you might have 100 as a value I'd increase that to numeric (5, 2)

Answer 3

NUMERIC(3,2) means: 3 digits in total, 2 after the decimal point. So you only have a single decimal before the decimal point.

Try NUMERIC(5,2) - three before, two after the decimal point.

Answer 4

Precision and scale are often misunderstood. In numeric(3,2) you want 3 digits overall, but 2 to the right of the decimal. If you want 15 => 15.00 so the leading 1 causes the overflow (since if you want 2 digits to the right of the decimal, there is only room on the left for one more digit). With 4,2 there is no problem because all 4 digits fit.