I learned something new about decimal data types in SQL Server recently. If you do something like this:

DECLARE @d DECIMAL(10,10)
SET @d = 1.0

You get this:

Msg 8115, Level 16, State 8, Line 2

Arithmetic overflow error converting numeric to data type numeric.

That threw me for a minute since I’d never experienced it, but then I realized what was going on. The declaration of a decimal data type goes like this:

DECLARE @d DECIMAL( @p, @s)

Where we have

• d – the name of the variable
• p = precision, the number of digits in the number.
• s = scale, the number of digits for the decimal

You can think of this as the following,

@d = nnnn.ssss

Where the count of n’s and s’s must equal p.

In the first example above, I’ve essentially declared:

@d = 0.ssssssssss

There’s no room for a digit, other than zero, to the left of the decimal. So you get an error. If I’d added one more digit to the p variable, like this:

 
DECLARE @d DECIMAL(11,10)
SET @d = 1.0

I don’t receive an error. Likewise, I can add multiple digits to the other side like this:

DECLARE @d DECIMAL(10,10)
SET @d = 0.999999999

Works fine.

I had never run into this because I don’t ever declare these the same. I almost always go larger than I need, and allow for more decimals. So for US money, I often declare things at decimal(10,3), giving me more space than needed. It pays to think ahead, and declare your variables properly, and understanding how they are structured is part of that.

Note: this applies to numeric types as well.