Scale vs. Precision - Why this definition?

Question

From the Microsoft documentation concerning T-SQL / SQL Server:

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.

To the naive non-expert (=me) this is always a bit confusing. I would have expected it the other way round.

• How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?
• How can I think of the number of decimals in terms of scale (how 'big' something is)?

Just to be clear: This is not a rant disguised as a question, and more than mere curiosity. I am convinced that there is a good reason for this terminology, and I think I can learn about databases by understanding the underlying thinking here. (If this question is considered too basic for here, I understand.)

Answer 1

Precision and Scale are based on the mathematical concept of Significant Figures. You're not alone in that the current definition/usage for these terms is confusing.

How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?

First, you must remember that everything in computers is represented in binary; however not all numbers are easily represented using binary syntax, specifically when it comes to certain fractions. One such fraction, ⅓, is actually impossible to represent in a computer. Even with the largest amount of memory possible, a computer will never be able to fully represent ⅓ in binary form. However, it can get more precise (e.g. close) to ⅓ with the more bits (e.g. and therefore decimal places) that are thrown at it.

Precision can therefore be thought of as how exact a number you want to represent. The more digits used to define the number, the more precise that number becomes.

How can I think of the number of decimals in terms of scale (how 'big' something is)?

Scale may best be explained with how we look at maps. A map of a state or province shows a much larger area with less detail, whereas a map of a city shows a smaller area at greater detail.

Scale can therefore be thought of as the amount of detail. The more detail we want to convey comes at the cost of showing less actual stuff.

I don't know if that helps you at all, but hopefully that helps provide some context behind the definitions of these terms.

Answer 2

As noted in ypercube's Wikipedia link, precision is related to the concept of significant figures (or significant digits).

Example: If you have a scale that measures mass to the milligram, and you have a precision of eight digits (three to the right of the decimal point, just for argument's sake), you can say something has a mass of 12345.000 grams, 12345.333 grams, or 12344.555 grams. If, on the other hand, you only have a precision of five digits, all of these would be listed as 12345 grams - therefore, your representation is less precise.

As far as scale - clearly, a number with a precision and scale of (8,0) can represent a number three orders of magnitude larger than one with a scale of (8,3) (12345678 > 12345.678). To my way of thinking, that is a valid definition of scale.

Answer 3

You miss one critical thing. Critical brutally in the past, critical for anyone not doing trivial work these days.

Storage.

Total number of digits has direct implication on storage, and thus on GB and IOPS needed. From that then you allocate a portion to precision.

Total number of digits has no meaning at all as everyday concept. But those of us who deal with large databases often live with it. Saving a handfull of bytes on a hundred billion row dable is not exactly bad for performance.

It is just the way of thinking - start with allocated storage, then divide into precision.