SQL Server decimal scale length - can be or has to be?

Question

I have really simply question about DECIMAL (and maybe NUMERIC) type in SQL Server 2008 R2.

MSDN said:

(scale) The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p.

I understand this following way:

• if I have DECIMAL(10, 5) - I am able to store 12345.12345 or 12345678.91.
• if I have DECIMAL(5, 5) - I can have 12345 or 1234.5 or 1.2345, etc...

Is it clear?

But I got this error message:

SELECT CAST(2.8514 AS DECIMAL(5,5))

Arithmetic overflow error converting numeric to data type numeric.

I thought 5,5 means I can have up to 5 digits and up to 5 CAN BE right of the decimal point.

As I tried:

SELECT CAST(12.851 AS DECIMAL(6,5)) - overflows too

however

SELECT CAST(1.23456 AS DECIMAL(6,5)) - is OK.

So what's the truth?

DECIMAL(a,b) says that I can have up to a digits and JUST b of them are right to the decimal point (and there rest a-b to the left to the dec. point)?

I'm really confused about statement in doc which is copied everywhere. Please take a while and explain me this simple thing.

Lot of thanks!

Solution

The easiest way to think of it (for me) is that precision is the total number of digits, of which scale is the number of digits to the right of the decimal point. So DECIMAL(p,s) means p-s digits to the left of the point, and s digits to the right of the point.

That explains all the conversion errors you're seeing: the 2.8514 cannot be decimal(5,5) because p-s = 0; 12.851 cannot be decimal(6,5) because p-s = 1 and so on.