principlesqlMinor
Scale vs. Precision - Why this definition?
Viewed 0 times
definitionthiswhyprecisionscale
Problem
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.
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.)
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.)
Solution
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.
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.
Context
StackExchange Database Administrators Q#184776, answer score: 9
Revisions (0)
No revisions yet.