300

I have the following column specified in a database: decimal(5,2)

How does one interpret this?

According to the properties on the column as viewed in SQL Server Management studio I can see that it means: decimal(Numeric precision, Numeric scale).

What do precision and scale mean in real terms?

It would be easy to interpret this as a decimal with 5 digits and two decimals places...ie 12345.12

P.S. I've been able to determine the correct answer from a colleague but had great difficulty finding an answer online. As such, I'd like to have the question and answer documented here on stackoverflow for future reference.

fredt
  • 24,044
  • 3
  • 40
  • 61
mezoid
  • 28,090
  • 37
  • 107
  • 148

4 Answers4

491

Numeric precision refers to the maximum number of digits that are present in the number.

ie 1234567.89 has a precision of 9

Numeric scale refers to the maximum number of decimal places

ie 123456.789 has a scale of 3

Thus the maximum allowed value for decimal(5,2) is 999.99

NinjaBeetle
  • 85
  • 1
  • 12
mezoid
  • 28,090
  • 37
  • 107
  • 148
  • 22
    Don't forget that if you're using a system that allows you to pre-define precision and scale of an input for a percentage in something like Microsoft Access, you must consider the percent as it's whole number form. In this case, 25.5% would require precision 4 and scale of 3 (not one) since we have to consider it as .255. I came across this problem early on and was stumped for a while wondering why scale 1 wasn't working. – Ashton Sheets Dec 05 '13 at 21:54
  • 1
    @mezoid What does a negative scale value mean? – Geek Jun 19 '14 at 13:20
  • @Geek According to http://technet.microsoft.com/en-us/library/ms187746.aspx The scale cannot be less than zero. 0 <= scale <= precision. Essentially a negative scale value would be meaningless. – mezoid Jun 19 '14 at 23:15
  • 5
    @mezoid: That is not true for all databases; e.g., in Oracle: ["What purpose could a negative scale fulfill? It allows you to round values to the left of the decimal place. Just as the NUMBER(5,2) rounded values to the nearest .01, so a NUMBER(5,-2) would round to the nearest 100..."](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055) – Air Sep 16 '14 at 18:31
  • @AirThomas Cool. I didn't know that. That's good to know. I've only dealt with SQL Server. – mezoid Sep 16 '14 at 23:40
  • +100 :); This helped me greatly understand DECIMAL! I used to think that precision was the number of non-decimal digits in a number. For example, I thought that DEC(5,2) could take 11111.22. – FearlessFuture Jan 07 '15 at 20:04
  • 1
    Shouldn't it be: "Numeric precision refers to the maximum number of digits that could be present in the number."? The exact number 123.5 could be of precision 10 as well, but there are no more digits to add. Or would this become 123.5000000? – Chris311 Dec 29 '15 at 11:41
114

Precision of a number is the number of digits.

Scale of a number is the number of digits after the decimal point.

What is generally implied when setting precision and scale on field definition is that they represent maximum values.

Example, a decimal field defined with precision=5 and scale=2 would allow the following values:

  • 123.45 (p=5,s=2)
  • 12.34 (p=4,s=2)
  • 12345 (p=5,s=0)
  • 123.4 (p=4,s=1)
  • 0 (p=0,s=0)

The following values are not allowed or would cause a data loss:

  • 12.345 (p=5,s=3) => could be truncated into 12.35 (p=4,s=2)
  • 1234.56 (p=6,s=2) => could be truncated into 1234.6 (p=5,s=1)
  • 123.456 (p=6,s=3) => could be truncated into 123.46 (p=5,s=2)
  • 123450 (p=6,s=0) => out of range

Note that the range is generally defined by the precision: |value| < 10^p ...

boumbh
  • 2,010
  • 1
  • 19
  • 21
  • 6
    Note that MS SQL Server wouldn't allow 12345 or 1234.56 because "[scale] is substracted from [precision] to determine the maximum number of digits to the left of the decimal point." (source: [decimal and numeric](http://technet.microsoft.com/en-us/library/ms187746.aspx)) – molnarm Feb 17 '14 at 10:48
  • How about `12345000`? Precision 5 or 8? If 5, with what Scale? Scale -3? – towi Mar 05 '15 at 14:11
  • @towi what does that mean? If you wanted to store that, you'd use 8,0. – Rob Grant Sep 23 '15 at 14:01
  • 2
    Nice answer, but why is `123450 (p=6,s=0)` out of range? 123450 has 6 digits and no digits after a point? – Matthias Burger Jan 31 '18 at 17:10
  • 3
    @MatthiasBurger `123450 (p=6,s=0)` would be out of range for a decimal field with 5 precision (as mentioned in the example). Because the precision of a number you want to store in a field must be less than or equal to the precision of the field. – domids Mar 23 '18 at 13:16
  • 3
    @DominikSeitz ah thx, I misunderstood the the answer of boumbh. 123450 is out of range for `(p=5,s=2)`. I understood 123450 was out of range for `(p=6,s=0)` – Matthias Burger Mar 23 '18 at 13:24
37

Precision, Scale, and Length in the SQL Server 2000 documentation reads:

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

Air
  • 8,274
  • 2
  • 53
  • 88
Chris
  • 6,914
  • 5
  • 54
  • 80
  • Thank you. I just realized that a piece of Delphi/Pascal code was using a scale of 0 to chop off the decimal part of float – Peter Chaula Aug 10 '18 at 10:24
1

Precision refers to the total number of digits while scale refers to the digits allowed after the decimal. The example quoted by would have a precision of 7 and a scale of 2.

Moreover, DECIMAL(precision, scale) is an exact value data type unlike something like a FLOAT(precision, scale) which stores approximate numeric data. For example, a column defined as FLOAT(7,4) is displayed as -999.9999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Let me know if this helps!

Key
  • 45
  • 1
  • 6
  • Is DECIMAL internally stored like integer and therefore is exact type? Float values had to be approximate due to its internal representation (as I remember) and cant be exact after certain scale number. – Arkemlar Oct 06 '22 at 07:49