3

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.

6

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))

Results:

| 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))

Results:

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.