16

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jan Drozen
  • 894
  • 2
  • 12
  • 28
  • `DECIMAL(5,5)` means: total of 5 digits, 5 of which are **after** the decimal point - so basically this is not a valid number... `DECIMAL(10,5)` means: total of 10 digits, 5 of which **after** the decimal point (and therefore 5 before the decimal point, too). It's not a ***CAN BE*** after the decimal point - it's a ***ARE AFTER*** the decimal point specification – marc_s Apr 05 '13 at 20:27
  • 2
    @marc_s I believe that's exactly why the OP is confused: the [documentation](http://msdn.microsoft.com/en-us/library/ms187746.aspx) only uses the word "can", so it's not really clear that decimal(5,5) 'reserves' 5 digits after the decimal point, whether you 'use' them or not. Perhaps it's obvious to more mathematically inclined people, of course (I'm not one of those people). – Pondlife Apr 05 '13 at 20:36
  • @Pondlife you are actually right about the documentation being confusing. Back in 2013, I submitted a bug report to MSDN, and they changed it to the wording I suggested in http://stackoverflow.com/questions/12207222/decimal-10-9-variable-cant-hold-the-number-50-sql-server-2008. – Jordan Rieger Nov 03 '16 at 17:53

1 Answers1

21

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.

Pondlife
  • 15,992
  • 6
  • 37
  • 51