On Fri, Jul 15, 2016 at 1:13 PM, Kevin Adler <kadler@xxxxxxxxxx> wrote:
From: John Yeung <gallium.arsenide@xxxxxxxxx>
What made me finally grok the terms, used this way, is thinking of
scientific notation and IEEE floating point.
I think I'm in agreement with you on what scale and precision means, yet
that's exactly contra to how CLI uses those terms.
I'm afraid we're not really. I just tried to explain how the SQL
terminology makes sense to me now, not how it's opposite to my way of
thinking.
Scale to me means "how
big is this thing" whereas precision means "how accurate is this thing".
Yes, we agree at this level. I mean, we both have the same
understanding of what the words "precision" and "scale" mean in
English.
But you have to be careful what "thing" it is you are talking about.
In CLI though, these terms are backwards from my expectations: precision
is "how big is this thing" and scale is "how accurate is this thing."
OK. When I went into my diatribe about scientific notation, and the
precision of measurements, I was talking in terms of the thing being
measured. That is, at "astronomical scale", the measurement might be
the distance between galaxies. At that scale, if the measurement is
precise down to a few million miles, then we're talking seriously good
precision. On the other hand, if you're working at subatomic scale,
and you can't get measurements any finer than microns, your precision
is so poor as to be unusable.
So precision of a *measurement* isn't tied to the physical size of the
thing we're measuring. But when it comes time to STORE our
measurement, we need *more space* if we want to preserve greater
precision! If I take a 4 megapixel photo of an object, and you take a
40 megapixel photo of the very same object, from the same angle at the
same distance, the size of the object hasn't changed, but you need 10
times as much space to store your data. Your file is "bigger". But
your picture has more resolution (i.e. greater precision).
So yeah, a higher *precision* data field is *bigger* in terms of
computer storage. I mean, bottom line is you need more bytes to store
more digits.
What you need to remember is that "scale" doesn't refer to the file
size of the photo. It refers to the size of the thing you took a
picture of. Maybe it was mountains. Maybe it was insects. If you use
the same camera for both, all of your photos are 40 megapixel,
regardless. You've got 40-megapixel precision.
So, in SQL, the precision of a field corresponds to the resolution of
the photo. The scale tells you whether you shot mountains or insects.
Mountains are certainly bigger than insects. Maybe a (9 0) field is
for mountains and a (9 6) field is for insects. The fact that they
both have 9 total digits tells you you've used the same camera for
both.
John Y.
midrange.com
