# Why doesn't `1` fit into a decimal(4, 4) column?

I have a `decimal(4, 4)` column in MS SQL Server 2008 R2. If I understand correctly, that means:

• `Precision` of 4, ie up to four digits can be stored after the decimal place
• `Scale` of 4, ie a a total of four digits can be stored

When I run an update command to set the column to `1` (`update myTable set myDecimalColumn=1`), I get this error:

Arithmetic overflow error converting numeric to data type numeric.

I don't understand how that is true. `1` has no digits after the decimal and is only one digit long.

The `Precision` specifies the total number of digits that can be stored, the `Scale` specifies, how many of those digits live behind the decimal point.

A `DECIMAL(4,4)` therefore has four digits and all are behind the decimal point. That means you can store values form -0.9999 to 0.9999. You cannot store -1 or 1 as they have a digit before the decimal point.

SQL Fiddle

Query 1:

``````SELECT CAST(-0.9999 as DECIMAL(4,4)),CAST(0.123456 as DECIMAL(4,4)),CAST(0.9999 as DECIMAL(4,4))
``````
``````| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -0.9999 |   0.1235 |   0.9999 |
``````

To store 1 you have to add an additional digit as in `DECIMAL(5,4)`. That allows you to store values from -9.9999 to 9.9999:

SQL Fiddle

Query 2:

``````SELECT CAST(-9.9999 as DECIMAL(5,4)),CAST(1 as DECIMAL(5,4)),CAST(9.9999 as DECIMAL(5,4))
``````
``````| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -9.9999 |        1 |   9.9999 |
``````
• I didn't realize scale was exclusively after the decimal. I thought it was up to that number after the decimal. – just.another.programmer Aug 12 '13 at 5:46