I'm baffled. I have a column in a SQL Server Express database called
"Longitude," which is a float. When I view the table in a DataGridView,
some of the numbers, which only have two decimal places in the database
show up with *15* decimal places and are ever so slightly off (in the
example in the subject line, by about 2E-15).
I'm not doing any operations on this column. It's just running a stored
procedure which performs a pretty basic SELECT on the table. If I run
the stored procedure in Management Studio Express, all numbers show up
fine (just two decimal places).
What's going on here?
Thanks,
-Dan
Jan 9 '07
130  6154 
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft.c om...
Mike C# <xy*@xyz.comwrote:
>"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft. com...
Why claim that it's
the type which is approximate rather than the conversion? Where's the
benefit?
Because some MVP will jump in and claim that the conversion cannot
technically be called "approximate". After all, the conversion is a
hard-coded set of instructions that perform the same function every time
and
produce the same value given the same input every time. Sounds fairly
exact
to me.
That means it's deterministic, not that it's exact. It certainly *is*
an approximation, and it's losing information. I'd certainly be
perfectly happy to argue the "approximate" side of that debate :)
We could debate the exactness there :) After all, the conversion process
boils down to a bunch of machine-level instructions that perform the exact
same task each and every time. So that in addition to being deterministic,
they perform a very exact process. There's nothing approximate about the
machine language instructions that perform the conversion; they are exactly
defined, exactly performed, and you retrieve an exact result.
Do either you or Aaron actually disagree that when you separate the
idea of "type" from "conversion" it's the *conversion* which is
approximate rather than the FLOAT type itself? If you do, it's probably
worth continuing to debate (despite my 5am post!) - if not, it's
presumably just a matter of how much we all do/don't care about making
the distinction between types and conversions. That's a matter of
opinion and probably not worth arguing about.
I can agree with that to an extent; however, the lossy conversion is
necessary because of the limitations of the storage format. Consider BCD
format which stores each digit in 3 bits. Absolutely no loss occurs during
the character conversion process from "28.08" to the internal BCD
representation, precisely because the format is not limited in that respect.
SQL is a highly abstract language, and as I mentioned most users and
developers don't care about the intricacies of IEEE floating point
representation, mantissas (mantissae?), etc. As Aaron pointed out, SQL
users tend to focus on higher level aspects, like if I put in 28.08 will I
get back 28.08? Or will I get 28.079237402371? That's the power of higher
level languages - you don't need to know the intricate details of the
hardware to do useful things.
LOL. Good thing I don't bbq!
-Dan "still has his eyebrows" The Man
Stephany Young wrote:
I have a fair idea. And they're the same people who get a surprise when they
light the barbecue after pouring petrol (gasoline) on the charcoal.
It's the best argument that there is for not allowing users to muck about
with things that they don't understand.
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
<snip>
fact that they knew the nature of its conversion and storage. Do you know
how many people I have seen choose FLOAT for storing monetary values like
prices, or even quantities? And do you know how surprised they are to
find out that SQL Server changed 28.08 to 28.0800000000002? SQL Server
</snip>
Aaron Bertrand [SQL Server MVP] <te*****@dnartreb.noraawrote:
DECIMAL is an approximate type with respect to some values which are
exactly representable in base 3. It is an exact type with respect to
values which are exactly representable in base 10.
</proposal>
Is there anything in there that you'd actually disagree with (rather
than just not caring about)?
I don't think there's any reason to point out that decimal can't store base
3 precisely, since this is completely irrelevant in SQL Server.
The reason for pointing out the situations in which decimal would be
"approximate" is to throw more light on why float is "approximate" with
regards to base 10. It shows (correctly, IMO) that there's not much
*fundamental* difference between the two types - just the base
involved. (The precision and scale being fixed or floating is another
difference, of course, but not related to this issue IMO.)
If you really like those definitions, then propose them to the
standards bodies and to Microsoft (connect.microsoft.com/sql). I
don't feel very inclined to adopt them.
I was merely trying to find some common ground between us.
The difference between those statements and the simplistic statement
that "FLOAT is approximate, DECIMAL is exact" is that the above make it
perfectly clear that it's only the base involved which determines the
"exactness", not anything else about the type.
Irrelevant in the context of this discussion, imho, since you cannot alter
the base for any of the numeric data types in SQL Server.
The context of this discussion, as far as I'm concerned at least, is a
consistent treatment of "approximate" compared with "exact". My
statements gave a consistent treatment, whereas I see inconsistency in
the way that float and decimal are described otherwise. There's a
context of "with respect to numbers which are exactly representable in
base 10" in the simplistic statement which I feel would be better made
explicit than implicit.
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
The reason for pointing out the situations in which decimal would be
"approximate" is to throw more light on why float is "approximate" with
regards to base 10. It shows (correctly, IMO) that there's not much
*fundamental* difference between the two types - just the base
involved.
And since the base is fixed and not negotiable in SQL Server, we'll just
have to agree to disagree.
I was merely trying to find some common ground between us.
I don't think you will, because I still consider FLOAT and REAL to be
approximate data types in SQL Server. I don't think you'll convince many
people to change their opinions by trying to segregate conversion from
storage, because quite frankly, 99% of them don't care, and the other 1% are
likely busy solving more important problems. :-)
A
Aaron Bertrand [SQL Server MVP] <te*****@dnartreb.noraawrote:
The reason for pointing out the situations in which decimal would be
"approximate" is to throw more light on why float is "approximate" with
regards to base 10. It shows (correctly, IMO) that there's not much
*fundamental* difference between the two types - just the base
involved.
And since the base is fixed and not negotiable in SQL Server, we'll just
have to agree to disagree.
Do you see no value in thought experiments then? Not interested in what
the impact of a base 3 type *would* be if it were introduced in the
next version of SQL Server? (No, I'm still not suggesting it will be.)
But you're right, we will have to agree to disagree.
I was merely trying to find some common ground between us.
I don't think you will, because I still consider FLOAT and REAL to be
approximate data types in SQL Server. I don't think you'll convince many
people to change their opinions by trying to segregate conversion from
storage, because quite frankly, 99% of them don't care, and the other 1% are
likely busy solving more important problems. :-)
I'll try for one last time to find common ground: do you agree that
your statements about exact/approximate are only true from a base 10
perspective?
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jon Skeet [C# MVP] <sk***@pobox.comwrote:
<snip>
You wrote elsewhere:
<quote>
The fact that you cannot accurately store a value that is not a member
of the set of valid values for a type is of no significance.
</quote>
The set of valid values for FLOAT doesn't include 28.08 in the same way
that the set of valid values for INTEGER doesn't include 28.08 and in
the same way that the set of valid values for NUMERIC(2,1) doesn't
include 28.08. I see those three situations as being the same, but
you're highlighting FLOAT as being "different". Why?
Just replying to myself now that I've found both parts of the
inconsistency. Here's Mike about FLOAT:
<quote>
T-SQL defines real and float as
"approximate" data types because using the IEEE standard binary
representation not all numbers in the range of valid values can be
stored in an exact representation.
....
Decimal and numeric are "exact" data types because they can represent
all the numbers in their range of valid values in an exact
representation. I.e., "28.08" is stored exactly as 28.08.
</quote>
Compare that with the quote quoted above (if you see what I mean).
It seems that in the case of INTEGER and DECIMAL, "the range of valid
values" is deemed to be "those which can be stored exactly" whereas for
FLOAT Mike deems it to be "anything between the minimum value and the
maximum value".
Either 28.08 shouldn't be regarded as being "in the range of valid
values" for FLOAT, *or* it should be regarded as being "in the range of
valid values" for DECIMAL(1,2). I'm happy to use either definition, so
long as it's used consistently.
If you apply the definition of "range of valid values" Mike has used
for FLOAT to INTEGER/DECIMAL, you would have to concede that they are
not exact: 1/3 is between the min and max values of DECIMAL(1,2) but is
not exactly representable, for example.
In reverse, if you apply the definition of "range of valid values" Mike
has used for INTEGER/DECIMAL to FLOAT, then your claim that "not all
members in the range of valid values can be stored in an exact
representation" is clearly incorrect (by definition).
I suspect that any definition which can be applied *consistently* to
INTEGER, DECIMAL and FLOAT but which deems FLOAT to be "approximate"
and INTEGER/DECIMAL to be "exact" by examining the "range of values"
has to specifically refer to base 10 representations of numbers. Of
course, I'd be happy to be disproved - Mike, care to construct such a
definition?
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jon Skeet wrote:
The reason for pointing out the situations in which decimal would be
"approximate" is to throw more light on why float is "approximate" with
regards to base 10. It shows (correctly, IMO) that there's not much
*fundamental* difference between the two types - just the base
involved.
It is justified, however, for reasons of human culture and in particular
accounting and legal requirements in accounting.
It's not a technical slight or insult; it's about people.
-- Barry
-- http://barrkel.blogspot.com/
Thanks, everyone, I think :) At least I can't complain that no one
responded to my question! Anyway, I definitely need to rethink when I
use float/double and when I use decimal. As for when to use numeric,
SQL Server docs says "numeric is functionally equivalent to decimal" so
I'm thinking...never?
As for the debate over what to call the different data types, the
conclusion that formed in my mind was something like this: The decimal
data type can store base-10 numbers that don't repeat infinitely
"exactly", while the float data type can store binary numbers that
don't repeat infinitely "exactly." Ask either type to store a base they
weren't designed for, however, and you could lose information during
the conversion, resulting in the stored number being an "inexact"
representation of the original number. Since we live in a base-10
world, and saying "inexact data type" is, well, a lot quicker than what
I just said, I think ANSI and whoever else decided to call floats
"inexact" data types were more than justified, even if their semantics
were imperfect.
-Dan
Aaron Bertrand [SQL Server MVP] wrote:
You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.
Well, yeah, Sherlock.
The OP passed 28.08 into the database. When he asked for the value back, he
got a different answer. He asked how to fix it, so I guess, yeah, our bias
in this case is toward using DECIMAL. Where, regardless of what fantasy
world you're converting to base 3 et. al., when you input 28.08 and then ask
for the value back, you're still going to get 28.08. This isn't about math
theory or MSDN or base 3 or 1/3 or some religious war about whether you
think the standards' definition of "approximate" is acceptable to you. It's
about storing a known value (not an expression, like 1/3) with a fixed
number of decimal places, and getting the same number back, every time, in a
deterministic fashion.
A
Thanks, everyone, I think :) At least I can't complain that no one
responded to my question! Anyway, I definitely need to rethink when I
use float/double and when I use decimal. As for when to use numeric,
SQL Server docs says "numeric is functionally equivalent to decimal" so
I'm thinking...never?
As for the debate over what to call the different data types, the
conclusion that formed in my mind was something like this: The decimal
data type can store base-10 numbers that don't repeat infinitely
"exactly", while the float data type can store binary numbers that
don't repeat infinitely "exactly." Ask either type to store a base they
weren't designed for, however, and you could lose information during
the conversion, resulting in the stored number being an "approximate"
representation of the original number. Since we live in a base-10
world, and saying "approximate data type" is, well, a lot quicker than
what
I just said, I think ANSI and whoever else decided to call floats
"approximate" data types were more than justified, even if their
semantics
were imperfect.
-Dan
Aaron Bertrand [SQL Server MVP] wrote:
You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.
Well, yeah, Sherlock.
The OP passed 28.08 into the database. When he asked for the value back, he
got a different answer. He asked how to fix it, so I guess, yeah, our bias
in this case is toward using DECIMAL. Where, regardless of what fantasy
world you're converting to base 3 et. al., when you input 28.08 and then ask
for the value back, you're still going to get 28.08. This isn't about math
theory or MSDN or base 3 or 1/3 or some religious war about whether you
think the standards' definition of "approximate" is acceptable to you. It's
about storing a known value (not an expression, like 1/3) with a fixed
number of decimal places, and getting the same number back, every time, in a
deterministic fashion.
A
Barry Kelly <ba***********@gmail.comwrote:
The reason for pointing out the situations in which decimal would be
"approximate" is to throw more light on why float is "approximate" with
regards to base 10. It shows (correctly, IMO) that there's not much
*fundamental* difference between the two types - just the base
involved.
It is justified, however, for reasons of human culture and in particular
accounting and legal requirements in accounting.
It's not a technical slight or insult; it's about people.
The reason DECIMAL exists is certainly about people/accounting/etc -
but I don't see that the differences between DECIMAL and FLOAT need to
made to seem greater than they are.
(The impression that decimal is exact and float/double are approximate
occurs in more than SQL land - it's a common belief in the C#/.NET
communities as well, usually where people don't really understand how
the types work under the hood.)
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jon Skeet wrote:
Barry Kelly <ba***********@gmail.comwrote:
The reason for pointing out the situations in which decimal would be
"approximate" is to throw more light on why float is "approximate" with
regards to base 10. It shows (correctly, IMO) that there's not much
*fundamental* difference between the two types - just the base
involved.
It is justified, however, for reasons of human culture and in particular
accounting and legal requirements in accounting.
It's not a technical slight or insult; it's about people.
The reason DECIMAL exists is certainly about people/accounting/etc -
but I don't see that the differences between DECIMAL and FLOAT need to
made to seem greater than they are.
It's probably because most people posting here are people, I expect :P
(The impression that decimal is exact and float/double are approximate
occurs in more than SQL land - it's a common belief in the C#/.NET
communities as well, usually where people don't really understand how
the types work under the hood.)
I think the people who understand less are better guided (i.e. more
easily, more emotionally, less intellectually demanding) by the rule of
thumb that decimal is exact, float / double is fast, and would thus
choose decimal where fidelity in especially important matters like money
is required. And the people who understand more aren't confused by the
shorthand.
In short, I'm not seeing who the victim is, unless one is trying to
eradicate ignorance from all programmers (I hopeless task, I fear).
-- Barry
-- http://barrkel.blogspot.com/
Barry Kelly <ba***********@gmail.comwrote:
(The impression that decimal is exact and float/double are approximate
occurs in more than SQL land - it's a common belief in the C#/.NET
communities as well, usually where people don't really understand how
the types work under the hood.)
I think the people who understand less are better guided (i.e. more
easily, more emotionally, less intellectually demanding) by the rule of
thumb that decimal is exact, float / double is fast, and would thus
choose decimal where fidelity in especially important matters like money
is required. And the people who understand more aren't confused by the
shorthand.
In short, I'm not seeing who the victim is, unless one is trying to
eradicate ignorance from all programmers (I hopeless task, I fear).
Well, I like to eradicate ignorance where I can. I'm not sure I see
that people are better guided by a "fudge" (as I see it) of the truth
rather than the truth itself. I've seen many similar situations where
over-simplifications (or just not mentioning the context, such as "in
base 10" in this case) have ended up doing more harm than good, and
where a more direct explanation is actually no harder to understand.
Of course, having a rule of thumb like the one above is absolutely fine
- it's when the *explanation* of the rule of thumb skirts around the
issue that I worry a bit, that's all.
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
over-simplifications (or just not mentioning the context, such as "in
base 10" in this case)
Since in SQL Server there is only base 10, this is like saying the speed
limit sign should have an asterisk stating that this is true as long as
you're driving a vehicle and not walking or flying a plane.
A
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
Compare that with the quote quoted above (if you see what I mean).
It seems that in the case of INTEGER and DECIMAL, "the range of valid
values" is deemed to be "those which can be stored exactly" whereas for
FLOAT Mike deems it to be "anything between the minimum value and the
maximum value".
When I spoke of the "range" of valid values, I should have used the more
proper term "set" of valid values, and I did correct that. My bad. Of
course I never claimed FLOAT was "inaccurate", as you indicated in a prior
post; so I guess we all make the occasional typo.
Riddle me this, Skeetman: When I store the FLOAT value 28.08 in SQL Server
2000 and SELECT it in Query Analyzer it returns 28.079999999999998. When I
retrieve the same value in .NET from the same table in SQL Server, it
returns what the OP discovered: 28.080000000000002. You indicated that
when assigning a floating point value of 28.08 to a variable on the .NET
side that you received a different number. My question to you is plain and
simple: Which one is the "exact" representation of 28.08? The fact that
three different methods using the IEEE standard format can interpret the
same data three different ways (and all Microsoft products by the way) makes
a pretty convincing argument for calling the IEEE format "approximate".
Either 28.08 shouldn't be regarded as being "in the range of valid
values" for FLOAT, *or* it should be regarded as being "in the range of
valid values" for DECIMAL(1,2). I'm happy to use either definition, so
long as it's used consistently.
If you feel like compiling and publishing a complete list of valid values
for the FLOAT and REAL types, I will be happy to concede this point. I
agree that FLOAT and REAL do have a set of valid values and a set of invalid
values. However, considering the precision and scale allowed with FLOAT and
REAL, and the upper and lower bounds involved, plus the fact that every
client seems to interpret the same FLOAT and REAL data differently, I
imagine it's going to be quite a chore. With the INT type, there is a
well-defined set of valid values: All of the integer values
between -2147483648 and 2147483647, inclusive. DECIMAL (1,2) isn't even a
valid construct. You can't have one total digit and two digits after the
decimal point. You can, however, have DECIMAL (3, 2) which would include
all of the values between -9.99 and +9.99 consisting of three total digits
and two digits after the decimal point. And that's the same no matter what
client you are using.
I'll be more than happy to start calling "FLOAT" and "REAL" exact data types
when you create and publish that complete list of the set of valid values
for FLOAT and REAL. I would suggest starting with the valid values as .NET
interprets them, and the second part of your treatise can include the valid
values as Enterprise Manager interprets them. The next section could cover
SQL 2005, and then it would be handy to have the list of valid values as
interpreted by SQL 2005's XQuery implementation (which I've confirmed is at
least slightly different from the SQL Server version - somewhere around the
36th digit after the decimal point or so).
If you apply the definition of "range of valid values" Mike has used
for FLOAT to INTEGER/DECIMAL, you would have to concede that they are
not exact: 1/3 is between the min and max values of DECIMAL(1,2) but is
not exactly representable, for example.
As I said, "set of valid values". If you care to provide the "set of valid
values" for FLOAT/REAL, I'll be happy to concede that point.
And by the way, your 1/3 operation is first rounded off/truncated to an
inexact value by the division operator; not by the assignment to
INTEGER/DECIMAL. During assignment it is rounded again; but it was already
made an inexact decimal value (or at least 1/3.0 would be; 1/3 would be
zero) by the division operator, so there is no reason to think that
assignment of an inexact decimal value to an INTEGER/DECIMAL value could be
made more exact by the simple act of assignment. Unless you feel otherwise?
In reverse, if you apply the definition of "range of valid values" Mike
has used for INTEGER/DECIMAL to FLOAT, then your claim that "not all
members in the range of valid values can be stored in an exact
representation" is clearly incorrect (by definition).
What happens if you apply the "set of valid values" that I used for
DECIMAL/NUMERIC? As I said, publish the complete set of valid values for
FLOAT/REAL as interpreted by the various platforms that use them, and I'll
happily concede that point to you.
I suspect that any definition which can be applied *consistently* to
INTEGER, DECIMAL and FLOAT but which deems FLOAT to be "approximate"
and INTEGER/DECIMAL to be "exact" by examining the "range of values"
has to specifically refer to base 10 representations of numbers. Of
course, I'd be happy to be disproved - Mike, care to construct such a
definition?
Sure, how about this: "Exact data types are those data types which can
store and retrieve single values coincident with their type without loss of
data, and without the introduction of error."
What was your definition again? Was it something like "if it can be stored
in 1's and 0's it's exact - regardless if what i put in is the same as what
i got back?"
Mike C# wrote:
[...] When I store the FLOAT value 28.08 in SQL Server
2000 and SELECT it in Query Analyzer it returns 28.079999999999998. When I
retrieve the same value in .NET from the same table in SQL Server, it
returns what the OP discovered: 28.080000000000002. [...]
After 65 messages that are more or less connected to the subject, we
are getting close to the problem of the original poster. If we convert
the value 28.08 in a IEEE 754 floating-point number with 64 bits
(equivalent to SQL Server's float or to .NET's double), we get the
value 0x403C147AE147AE14 (in hex), which would be exactly
28.0799999999999982946974341757595539093017578125 in decimal (with the
help of http://aspspider.net/jskeet/ExactDouble.aspx). This value
should be displayed as 28.08, because the digit 8 in the "...9982..."
is beyond the last digit that can be accurately stored. However, Query
Analyzer incorrectly displays it as 28.079999999999998 (note that
Management Studio displays it correctly), probably because QA converts
the value into another floating point format before displaying it. In
some .NET tools, we get the value 28.080000000000002, because the value
was converted to yet another floating point format (maybe extended
precision, with 80 bits).
I think that the above explanation is closer to the real answer to the
question of the original poster. If we would use only a 64-bit IEEE 754
floating-point number (converting it from/to decimal according to the
conversion rules defined in the standard), we would have no problems
with this particular number (28.08). It's the conversion between
different floating-point formats that causes problems in this
particular case.
Razvan
Mike C# <xy*@xyz.comwrote:
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
Compare that with the quote quoted above (if you see what I mean).
It seems that in the case of INTEGER and DECIMAL, "the range of valid
values" is deemed to be "those which can be stored exactly" whereas for
FLOAT Mike deems it to be "anything between the minimum value and the
maximum value".
When I spoke of the "range" of valid values, I should have used the more
proper term "set" of valid values, and I did correct that. My bad. Of
course I never claimed FLOAT was "inaccurate", as you indicated in a prior
post; so I guess we all make the occasional typo.
Riddle me this, Skeetman: When I store the FLOAT value 28.08 in SQL Server
2000 and SELECT it in Query Analyzer it returns 28.079999999999998. When I
retrieve the same value in .NET from the same table in SQL Server, it
returns what the OP discovered: 28.080000000000002. You indicated that
when assigning a floating point value of 28.08 to a variable on the .NET
side that you received a different number. My question to you is plain and
simple: Which one is the "exact" representation of 28.08?
It depends on the type used, of course - double in .NET (which is what
I used) is different to FLOAT on .NET. If you tried to express 1.2345
as NUMERIC(1,2) and NUMERIC(1,3) you'd get different answers, wouldn't
you? Same thing here. Different precision gives different results. For
example, when I use float instead of double, the exact value is:
28.0799999237060546875. It also depends on how the value is formatted
afterwards - I've been giving the exact, non-truncated values
The fact that
three different methods using the IEEE standard format can interpret the
same data three different ways (and all Microsoft products by the way) makes
a pretty convincing argument for calling the IEEE format "approximate".
Nope, just that the precisions used are different.
Either 28.08 shouldn't be regarded as being "in the range of valid
values" for FLOAT, *or* it should be regarded as being "in the range of
valid values" for DECIMAL(1,2). I'm happy to use either definition, so
long as it's used consistently.
If you feel like compiling and publishing a complete list of valid values
for the FLOAT and REAL types, I will be happy to concede this point.
I'll do that if you publish a complete list of valid values for
NUMERIC(38,20).
I can give you a program in C# which will print out every valid float
or double (exactly) if you're willing to wait a very long time
though... that's very easy.
I agree that FLOAT and REAL do have a set of valid values and a set of invalid
values. However, considering the precision and scale allowed with FLOAT and
REAL, and the upper and lower bounds involved, plus the fact that every
client seems to interpret the same FLOAT and REAL data differently, I
imagine it's going to be quite a chore. With the INT type, there is a
well-defined set of valid values: All of the integer values
between -2147483648 and 2147483647, inclusive. DECIMAL (1,2) isn't even a
valid construct. You can't have one total digit and two digits after the
decimal point. You can, however, have DECIMAL (3, 2) which would include
all of the values between -9.99 and +9.99 consisting of three total digits
and two digits after the decimal point. And that's the same no matter what
client you are using.
I'll be more than happy to start calling "FLOAT" and "REAL" exact data types
when you create and publish that complete list of the set of valid values
for FLOAT and REAL. I would suggest starting with the valid values as .NET
interprets them, and the second part of your treatise can include the valid
values as Enterprise Manager interprets them.
Well, here's the .NET one for double: - there'll be a lot of NaN values
in there because I need to go to work in a minute, but it wouldn't take
more than another 10 minutes at most to get rid of those. You'll need
DoubleConverter.cs from http://www.yoda.arachsys.com/csharp/floatingpoint.html to compile
against. Don't expect it to finish any time soon though - just as
printing out all the NUMERIC(38,20) values would take rather a long
time.
using System;
class Test
{
static void Main()
{
// It's fiddly getting a "for" loop to show all values
for (long l=long.MinValue; l < long.MaxValue; l++)
{
ShowDouble(l);
}
ShowDouble(long.MaxValue);
}
static void ShowDouble(long x)
{
double d = BitConverter.Int64BitsToDouble(x);
Console.WriteLine (DoubleConverter.ToExactString(d));
}
}
The next section could cover
SQL 2005, and then it would be handy to have the list of valid values as
interpreted by SQL 2005's XQuery implementation (which I've confirmed is at
least slightly different from the SQL Server version - somewhere around the
36th digit after the decimal point or so).
I'd have to look into both of those...
If you apply the definition of "range of valid values" Mike has used
for FLOAT to INTEGER/DECIMAL, you would have to concede that they are
not exact: 1/3 is between the min and max values of DECIMAL(1,2) but is
not exactly representable, for example.
As I said, "set of valid values". If you care to provide the "set of valid
values" for FLOAT/REAL, I'll be happy to concede that point.
Done for double in .NET above.
And by the way, your 1/3 operation is first rounded off/truncated to an
inexact value by the division operator; not by the assignment to
INTEGER/DECIMAL.
When I've written 1/3, I've always meant the number "a third", i.e. 0.1
in base 3. I should have made that clear. No more division is required
for that than considing 1/10, 0.1 base 10.
During assignment it is rounded again; but it was already
made an inexact decimal value (or at least 1/3.0 would be; 1/3 would be
zero) by the division operator, so there is no reason to think that
assignment of an inexact decimal value to an INTEGER/DECIMAL value could be
made more exact by the simple act of assignment. Unless you feel otherwise?
See above - if we had a base 3 type (which would just need to store an
integer and the number of times that integer is logically divided by 3,
just as NUMERIC does with 10) it could exactly represent "a third".
In reverse, if you apply the definition of "range of valid values" Mike
has used for INTEGER/DECIMAL to FLOAT, then your claim that "not all
members in the range of valid values can be stored in an exact
representation" is clearly incorrect (by definition).
What happens if you apply the "set of valid values" that I used for
DECIMAL/NUMERIC? As I said, publish the complete set of valid values for
FLOAT/REAL as interpreted by the various platforms that use them, and I'll
happily concede that point to you.
Having done .NET, do you particularly want me to do the SQL version?
For every precision available? I could look into it tonight, but I
suspect it would be a bit of a waste of time...
I suspect that any definition which can be applied *consistently* to
INTEGER, DECIMAL and FLOAT but which deems FLOAT to be "approximate"
and INTEGER/DECIMAL to be "exact" by examining the "range of values"
has to specifically refer to base 10 representations of numbers. Of
course, I'd be happy to be disproved - Mike, care to construct such a
definition?
Sure, how about this: "Exact data types are those data types which can
store and retrieve single values coincident with their type without loss of
data, and without the introduction of error."
Right - in that case, I claim that FLOAT and REAL are exact types, and
I *certainly* claim that the above means that the fact that 28.08 can't
be stored as a FLOAT does *not* make FLOAT approximate, contrary to
some previous statements.
What was your definition again? Was it something like "if it can be stored
in 1's and 0's it's exact - regardless if what i put in is the same as what
i got back?"
I'm not sure I ever provided a definition...
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jon Skeet [ C# MVP ] wrote:
Riddle me this, Skeetman: When I store the FLOAT value 28.08 in SQL Server
2000 and SELECT it in Query Analyzer it returns 28.079999999999998. When I
retrieve the same value in .NET from the same table in SQL Server, it
returns what the OP discovered: 28.080000000000002. You indicated that
when assigning a floating point value of 28.08 to a variable on the .NET
side that you received a different number. My question to you is plain and
simple: Which one is the "exact" representation of 28.08?
It depends on the type used, of course - double in .NET (which is what
I used) is different to FLOAT on .NET. If you tried to express 1.2345
as NUMERIC(1,2) and NUMERIC(1,3) you'd get different answers, wouldn't
you? Same thing here. Different precision gives different results. For
example, when I use float instead of double, the exact value is:
28.0799999237060546875. It also depends on how the value is formatted
afterwards - I've been giving the exact, non-truncated values
<snip>
Now that I'm at a machine with SQL server on it... I can't actually get
SQL server to format a double exactly as a string.
However, if you convert the value to BINARY, you end up with the raw 64
bit value, which can be converted to an exact string with a .NET
program (and is the same value as (double)28.08 in .NET).
So, if you require data to be stored/retrieved via strings, it looks
like FLOAT/REAL may have problems due to a poor set of conversion
functions (or my ignorance, of course) - but you *can* store and
retrieve values exactly, via the BINARY type.
Jon
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft.c om...
>"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
Riddle me this, Skeetman: When I store the FLOAT value 28.08 in SQL
Server
2000 and SELECT it in Query Analyzer it returns 28.079999999999998. When
I
retrieve the same value in .NET from the same table in SQL Server, it
returns what the OP discovered: 28.080000000000002. You indicated that
when assigning a floating point value of 28.08 to a variable on the .NET
side that you received a different number. My question to you is plain
and
simple: Which one is the "exact" representation of 28.08?
It depends on the type used, of course - double in .NET (which is what
I used) is different to FLOAT on .NET. If you tried to express 1.2345
as NUMERIC(1,2) and NUMERIC(1,3) you'd get different answers, wouldn't
you? Same thing here. Different precision gives different results. For
example, when I use float instead of double, the exact value is:
28.0799999237060546875. It also depends on how the value is formatted
afterwards - I've been giving the exact, non-truncated values
However, in the case of retrieving the value from the same database on the
same instance of SQL Server using two different clients, the value was
stored exactly the same in the database, but the results were:
28.079999999999998 and 28.080000000000002. To what kind of rounding error
do you attribute a difference of 0.000000000000004?
Nope, just that the precisions used are different.
In the two items given above the precisions used are the exact same since it
is the exact same data type stored in the exact same database. To what do
you attribute that difference?
>If you feel like compiling and publishing a complete list of valid values
for the FLOAT and REAL types, I will be happy to concede this point.
I'll do that if you publish a complete list of valid values for
NUMERIC(38,20).
Sure:
DECLARE @i NUMERIC (38, 20)
SELECT @i = -999999999999999999.99999999999999999999
WHILE 1 = 1
BEGIN
PRINT @i
SELECT @i = @i + 0.00000000000000000001
IF @i = 999999999999999999.99999999999999999999
BEGIN
PRINT @i
BREAK
END
END
Do you want me to email you the full list? Currently there is about 3.19 GB
of data in the file, and it is not finished yet. I'm still waiting on a
complete list of valid values for SQL FLOAT/REAL. Once we have that we'll
have to somehow explain away the discrepancies between different clients
accessing the same data; but I'm sure you have that covered as well.
I can give you a program in C# which will print out every valid float
or double (exactly) if you're willing to wait a very long time
though... that's very easy.
I'm actually not interested in C# valid "float" and "double". I'm
interested in SQL FLOAT and REAL, as specified in this message already. I'm
interested in C# only so far as when used as a client it seems to represent
certain "exact" SQL FLOAT and REAL values as differently from other clients
like Query Analyzer.
Well, here's the .NET one for double: - there'll be a lot of NaN values
in there because I need to go to work in a minute, but it wouldn't take
more than another 10 minutes at most to get rid of those. You'll need
DoubleConverter.cs from
http://www.yoda.arachsys.com/csharp/floatingpoint.html to compile
against. Don't expect it to finish any time soon though - just as
printing out all the NUMERIC(38,20) values would take rather a long
time.
You did not seem to account for the fact that .NET obviously can change the
"exact" value stored by SQL FLOAT and REAL types to different "exact"
values.
I'd have to look into both of those...
Indeed.
>As I said, "set of valid values". If you care to provide the "set of
valid
values" for FLOAT/REAL, I'll be happy to concede that point.
Done for double in .NET above.
By your own concession that has absolutely nothing to do with SQL FLOAT and
REAL, and I'm not interested in a .NET representation of .NET floats and
doubles. I am interested in a .NET representation of SQL FLOATs and REALs.
When I've written 1/3, I've always meant the number "a third", i.e. 0.1
in base 3. I should have made that clear. No more division is required
for that than considing 1/10, 0.1 base 10.
It doesn't matter what you mean; I saw you mention earlier that someone said
"1/3" is not a number, and I am inclined to agree. In SQL "1/3" is not a
number; it is a divisor, a division symbol, and a dividend. As you already
know, it is the same thing in C#.
See above - if we had a base 3 type (which would just need to store an
integer and the number of times that integer is logically divided by 3,
just as NUMERIC does with 10) it could exactly represent "a third".
It could, but it doesn't since we are not using Base-3. When you show me a
standard Base-3 datatype in SQL, or even in C# for that matter, I'll be
happy to discuss Base-3. Ditto for Base-4, Base-38, Base-109837, or
whatever other bases you care to discuss. As it stands Base-3 is a
non-entity.
Having done .NET, do you particularly want me to do the SQL version?
For every precision available? I could look into it tonight, but I
suspect it would be a bit of a waste of time...
Yes, because as you stated yourself, the SQL version and .NET versions are
different.
>Sure, how about this: "Exact data types are those data types which can
store and retrieve single values coincident with their type without loss
of
data, and without the introduction of error."
Right - in that case, I claim that FLOAT and REAL are exact types, and
I *certainly* claim that the above means that the fact that 28.08 can't
be stored as a FLOAT does *not* make FLOAT approximate, contrary to
some previous statements.
28.08 is coincident with the FLOAT and REAL data types, but when this value
is stored data is lost and error is introduced. That makes it approximate.
28.08 is also coincident with the NUMERIC(4, 2) type, and when this value is
stored no data is lost and no error is introduced. Finally, 28.08 is not
coincident with INTEGER type.
>What was your definition again? Was it something like "if it can be
stored
in 1's and 0's it's exact - regardless if what i put in is the same as
what
i got back?"
I'm not sure I ever provided a definition...
That was just what I gleaned from your conversation thus far. If you have a
definition, feel free to throw it out there for the world.
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:11*********************@p59g2000hsd.googlegro ups.com...
Jon Skeet [ C# MVP ] wrote:
Riddle me this, Skeetman: When I store the FLOAT value 28.08 in SQL
Server
2000 and SELECT it in Query Analyzer it returns 28.079999999999998.
When I
retrieve the same value in .NET from the same table in SQL Server, it
returns what the OP discovered: 28.080000000000002. You indicated
that
when assigning a floating point value of 28.08 to a variable on the
.NET
side that you received a different number. My question to you is plain
and
simple: Which one is the "exact" representation of 28.08?
It depends on the type used, of course - double in .NET (which is what
I used) is different to FLOAT on .NET. If you tried to express 1.2345
as NUMERIC(1,2) and NUMERIC(1,3) you'd get different answers, wouldn't
you? Same thing here. Different precision gives different results. For
example, when I use float instead of double, the exact value is:
28.0799999237060546875. It also depends on how the value is formatted
afterwards - I've been giving the exact, non-truncated values
<snip>
Now that I'm at a machine with SQL server on it... I can't actually get
SQL server to format a double exactly as a string.
However, if you convert the value to BINARY, you end up with the raw 64
bit value, which can be converted to an exact string with a .NET
program (and is the same value as (double)28.08 in .NET).
So, if you require data to be stored/retrieved via strings, it looks
like FLOAT/REAL may have problems due to a poor set of conversion
functions (or my ignorance, of course) - but you *can* store and
retrieve values exactly, via the BINARY type.
But that doesn't really do you any good, does it?
The benefit is that you're left with a useful definition. By your
reasoning, no datatype can ever be "exact" so the term is rendered
essentially useless. We can't ever store the "exact" value of 1/3 in
any meaningful way.
No, by my definition pretty much all datatypes are exact. (I can't
think of any that aren't, offhand.) Conversions between datatypes (at
least numeric ones) tend to be lossy.
Either way you render the definition useless. The way it's used in SQL
explains the behavior that can be expected.
Let's look at another example - INTEGER. Is INTEGER approximate? I
wouldn't say so - but if you tried to convert 28.08 into an INTEGER,
you'd certainly lose information, because that conversion is lossy.
Is INTEGER described as an "approximate" type by ANSI? I doubt it
somehow...
When you convert 28.02 into an integer you are storing it as 28. Not
an approximation, but exactly 28. The definition describes the
behavior of the data type.
In this case the terminology is more or less describing the way the
datatype can be expected to perform. When storing a valid value
(28.08) in a DECIMAL column you can expect to get exactly the same
value back again. Store the same value in FLOAT and you get an
approximation.
So when there's no lossy conversion involved, there's no loss of
information, but when there *is* a lossy conversion involved,
information is lost. That's not exactly a surprise, and has nothing to
do with the types themselves, as far as I can see.
Exactly - but the definitions are there to describe to a user (like our
OP) who wants to know which data type will give him the same number he
intended to store.
I don't see why people believe it's so difficult to explain/understand
that it's the conversion which is lossy, not the type.
It's not that it's difficult to explain or understand; it's just not
the intent of the defintion.
Note that if you pass in floating binary point data rather than decimal
data, you *won't* lose information - because hey, again, there's no
conversion involved. That isn't clear if you regard the type itself as
being lossy, but it's absolutely obvious when you separate conversions
from types.
Note that the OP asked why he was getting a 28.080000000000002 instead
of 28.08 :)
Using the terminology common to SQL, we can tell him to use one of the
various data types described as "exact" rather than the "approximate"
data type he is using now. Simple, correct answer that solves his
problem immediately.
Or, we could give him the nitty-gritty details of converting base2
numbers to base10, which while certainly interesting isn't all that
helpful.
"Razvan Socol" <rs****@gmail.comwrote in message
news:11**********************@o58g2000hsb.googlegr oups.com...
Mike C# wrote:
>[...] When I store the FLOAT value 28.08 in SQL Server
2000 and SELECT it in Query Analyzer it returns 28.079999999999998. When
I
retrieve the same value in .NET from the same table in SQL Server, it
returns what the OP discovered: 28.080000000000002. [...]
After 65 messages that are more or less connected to the subject, we
are getting close to the problem of the original poster. If we convert
the value 28.08 in a IEEE 754 floating-point number with 64 bits
(equivalent to SQL Server's float or to .NET's double), we get the
value 0x403C147AE147AE14 (in hex), which would be exactly
28.0799999999999982946974341757595539093017578125 in decimal (with the
help of http://aspspider.net/jskeet/ExactDouble.aspx). This value
should be displayed as 28.08, because the digit 8 in the "...9982..."
is beyond the last digit that can be accurately stored. However, Query
Analyzer incorrectly displays it as 28.079999999999998 (note that
Management Studio displays it correctly), probably because QA converts
the value into another floating point format before displaying it. In
some .NET tools, we get the value 28.080000000000002, because the value
was converted to yet another floating point format (maybe extended
precision, with 80 bits).
Actually if the "exact" value that is stored is
28.0799999999999982946974341757595539093017578125, then Query Analyzer is
correct in displaying it as 28.079999999999998, since the 2 does not round
up the 8. However, the only digits in this number that *were* accurately
stored are the leading "28.0".
I think that the above explanation is closer to the real answer to the
question of the original poster. If we would use only a 64-bit IEEE 754
floating-point number (converting it from/to decimal according to the
conversion rules defined in the standard), we would have no problems
with this particular number (28.08). It's the conversion between
different floating-point formats that causes problems in this
particular case.
The fact remains that the value 28.08 is put into SQL Server and it is
stored as an "exact" binary representation of a *completely different
number*. This is exacerbated by the clients' handling of the binary
representation, but the number 28.08 is never even stored in the database at
all.
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:uc*************@TK2MSFTNGP04.phx.gbl...
>over-simplifications (or just not mentioning the context, such as "in
base 10" in this case)
Since in SQL Server there is only base 10, this is like saying the speed
limit sign should have an asterisk stating that this is true as long as
you're driving a vehicle and not walking or flying a plane.
Not a bad idea :) How about posting all speed limit signs in Base-5?
"Mike C#" <xy*@xyz.comwrote in message
news:u$**************@TK2MSFTNGP02.phx.gbl...
By the way, here's shorthand for the set of valid values for NUMERIC(38,
20):
The set of valid values for NUMERIC(38, 20) includes all decimal numbers
with 18 or fewer digits before the decimal point and 20 or fewer digits
after the decimal point.
Mike C# wrote:
Actually if the "exact" value that is stored is
28.0799999999999982946974341757595539093017578125, then Query Analyzer is
correct in displaying it as 28.079999999999998, since the 2 does not round
up the 8.
No, it is not correct. It should display it as 28.08, because the digit
8 makes the previous 9 to round up. The digit 8 is beyond the last
digit that can be accurately stored, so it should not be displayed (it
should only be considered for rounding the previous digit). You can
understand which is the last digit that can be accurately stored, by
looking at the neighbouring values:
0x403C147AE147AE13=
28.07999999999999474198375537525862455368041992187 5
(this value is used for numbers between 28.079999999999993 and
28.079999999999996)
0x403C147AE147AE14=
28.0799999999999982946974341757595539093017578125
(this value is used for numbers between 28.079999999999997 and
28.080000000000000)
0x403C147AE147AE15=
28.08000000000000184741111297626048326492309570312 5
(this value is used for numbers between 28.080000000000001 and
28.080000000000003)
Since the digit 8 in the "...9982..." could be just as well a 7 or a 9
(and the number would still have the same representation), this digit
should not be displayed.
I think that this is called a "guard digit" (and it seems to be
implemented correctly in SQL Management Studio). I don't know where it
is specified in the IEEE standard itself, but you may get a more
detailed understading of the subject by from the following page: http://docs.sun.com/source/806-3568/ncg_goldberg.html
Razvan
"Razvan Socol" <rs****@gmail.comwrote in message
news:11**********************@k58g2000hse.googlegr oups.com...
Mike C# wrote:
>Actually if the "exact" value that is stored is
28.0799999999999982946974341757595539093017578125 , then Query Analyzer is
correct in displaying it as 28.079999999999998, since the 2 does not
round
up the 8.
No, it is not correct. It should display it as 28.08, because the digit
8 makes the previous 9 to round up. The digit 8 is beyond the last
digit that can be accurately stored, so it should not be displayed (it
should only be considered for rounding the previous digit). You can
understand which is the last digit that can be accurately stored, by
looking at the neighbouring values:
You say that the digit "8" is beyond the last digit that *can* be accurately
stored, but the second digit past the decimal point is not accurately
stored; therefore the "7" is beyond the last digit which *was* accurately
stored:
Number entered: 28.08
Number stored: 28.0799999999999982946974341757595539093017578125
Accurate digits: 28.0 (first three digits)
First inaccurate digit: 7 (fourth digit)
If the "8" is beyond the last digit which *can* be accurately stored, then
you seem to be saying that "28.07999999999999" is accurately stored.
However, the number which was stored was "28.08"; therefore
"28.07999999999999" is inaccurate.
As the OP (!), I can tell you that, yes, this whole debate has been
interesting, but the helpful part is knowing which data type to use
when.
For the particular case at hand (trying to display lat/long values back
to the user exactly as originally entered), I decided to change the
data type of my lat/long columns in SQL to DECIMAL(18, 15). Note, more
precision than necessary for this application but just wanted to see
what happens.
The result: When displayed in a .NET DataGridView, every number
displayed had 15 decimal digits. So, a number orginally entered as
"23.5" showed up as "23.500000000000000." Not what I was hoping for.
Using floats, the DataGridView would display only as many digits as
necessary. With decimals, it shows them all, whether they add useful
information or not.
Apparently, I want a hybrid of the two. Give me VARDECIMAL please :) I
want the ability to store any decimal number within a reasonable range
and have it spit back exactly what I entered but without any
unnecessary decimal places.
Anyway, my stop-gap measure was to change the format of the affected
columns in the DataGridView to "0.000," which limits the displayed
precision to three decimal places. This certainly improves readability,
but it will lead to a loss of accuracy if the user tries to edit one of
these formatted cell, because the missing digits don't naturally
reappear.
Of course, I could write some custom code to make the remaining digits
appear, but, besides being a pain in the ass, if all of a sudden 13
trailing zeros appear when the user goes into edit mode, that's not
really a good thing for usability, etc.
I'm starting to wonder if I should just store the lat/long values as
VARCHAR and convert them to decimals when it comes time to actually
display them on the map or do calculations with them, but something
tells me that will cause a whole other set of problems.
Color me frustrated.
Help greatly appreciated.
-Dan
Shuurai wrote:
The benefit is that you're left with a useful definition. By your
reasoning, no datatype can ever be "exact" so the term is rendered
essentially useless. We can't ever store the "exact" value of 1/3 in
any meaningful way.
No, by my definition pretty much all datatypes are exact. (I can't
think of any that aren't, offhand.) Conversions between datatypes (at
least numeric ones) tend to be lossy.
Either way you render the definition useless. The way it's used in SQL
explains the behavior that can be expected.
Let's look at another example - INTEGER. Is INTEGER approximate? I
wouldn't say so - but if you tried to convert 28.08 into an INTEGER,
you'd certainly lose information, because that conversion is lossy.
Is INTEGER described as an "approximate" type by ANSI? I doubt it
somehow...
When you convert 28.02 into an integer you are storing it as 28. Not
an approximation, but exactly 28. The definition describes the
behavior of the data type.
In this case the terminology is more or less describing the way the
datatype can be expected to perform. When storing a valid value
(28.08) in a DECIMAL column you can expect to get exactly the same
value back again. Store the same value in FLOAT and you get an
approximation.
So when there's no lossy conversion involved, there's no loss of
information, but when there *is* a lossy conversion involved,
information is lost. That's not exactly a surprise, and has nothing to
do with the types themselves, as far as I can see.
Exactly - but the definitions are there to describe to a user (like our
OP) who wants to know which data type will give him the same number he
intended to store.
I don't see why people believe it's so difficult to explain/understand
that it's the conversion which is lossy, not the type.
It's not that it's difficult to explain or understand; it's just not
the intent of the defintion.
Note that if you pass in floating binary point data rather than decimal
data, you *won't* lose information - because hey, again, there's no
conversion involved. That isn't clear if you regard the type itself as
being lossy, but it's absolutely obvious when you separate conversions
from types.
Note that the OP asked why he was getting a 28.080000000000002 instead
of 28.08 :)
Using the terminology common to SQL, we can tell him to use one of the
various data types described as "exact" rather than the "approximate"
data type he is using now. Simple, correct answer that solves his
problem immediately.
Or, we could give him the nitty-gritty details of converting base2
numbers to base10, which while certainly interesting isn't all that
helpful.
Daniel Manes wrote:
[...] Apparently, I want a hybrid of the two. Give me VARDECIMAL please :) [...]
Actually, there is something called vardecimal in SQL Server 2005 SP2,
but I think you are not refferring to this. It is a "storage format",
not a "data type" (it is enabled at the table level, after configuring
a database-level option). It allows decimal columns to use less space,
but it only works on Enterprise Edition (and Developer Edition and
Evaluation Edition), and only for databases using the Simple recovery
model. For more informations, see the updated Books Online. (Note:
Currently, SP2 is in the CTP phase)
Razvan
The result: When displayed in a .NET DataGridView, every number
displayed had 15 decimal digits. So, a number orginally entered as
"23.5" showed up as "23.500000000000000." Not what I was hoping for.
So why don't you use the ToString() and supply format arguments (e.g.
#,##0.000)? That's what they're there for, and that tier is the proper
place to handle presentation / formatting. The database's job is to store
and retrieve data, not to make it look pretty. What if SQL Server returned
only non-zero trailing decimal values, but I *want* all the decimal places,
for example to make sure the values have a consistent number of digits? If
the database determined how to present the data, one of us would be having
fits. In the meantime, you have a good workaround, imho.
Apparently, I want a hybrid of the two. Give me VARDECIMAL please :)
SQL Server 2005's Service Pack 2 adds VARDECIMAL, but it is not implemented
the way you desire. It is about storage, not presentation. Here are a
couple of articles that describe it. http://sqlservergems.blogspot.com/20...ardecimal.html http://weblogs.sqlteam.com/mladenp/a.../10/19546.aspx http://blogs.msdn.com/sqlserverstora...ge-format.aspx
Anyway, my stop-gap measure was to change the format of the affected
columns in the DataGridView to "0.000," which limits the displayed
precision to three decimal places. This certainly improves readability,
but it will lead to a loss of accuracy if the user tries to edit one of
these formatted cell, because the missing digits don't naturally
reappear.
Of course, I could write some custom code to make the remaining digits
appear, but, besides being a pain in the ass, if all of a sudden 13
trailing zeros appear when the user goes into edit mode, that's not
really a good thing for usability, etc.
That is debatable, I suppose. If the users are going to be editing the 13th
decimal place when it is non-zero, it is arguable that they aren't going to
complain much to see digits there when they are simply zeros. In any case,
SQL Server is not in a position to magically fix this problem for you, nor
would I expect it to be.
I'm starting to wonder if I should just store the lat/long values as
VARCHAR and convert them to decimals when it comes time to actually
display them on the map or do calculations with them, but something
tells me that will cause a whole other set of problems.
Yes, it will.
Aaron Bertrand [SQL Server MVP] wrote:
The result: When displayed in a .NET DataGridView, every number
displayed had 15 decimal digits. So, a number orginally entered as
"23.5" showed up as "23.500000000000000." Not what I was hoping for.
So why don't you use the ToString() and supply format arguments (e.g.
#,##0.000)? That's what they're there for, and that tier is the proper
place to handle presentation / formatting. The database's job is to store
and retrieve data, not to make it look pretty. What if SQL Server returned
only non-zero trailing decimal values, but I *want* all the decimal places,
for example to make sure the values have a consistent number of digits? If
the database determined how to present the data, one of us would be having
fits. In the meantime, you have a good workaround, imho.
Two things:
1. With floats, you can specify the size of the mantissa, but the
decimal place can be located anywhere. With decimals, you need to
specify where the decimal place is located or it defaults to zero (no
decimal places). In .NET, double and decimal seem to behave pretty much
the same in this regard--the decimal point really can float. Hence my
thought that VARDECIMAL would be nice.
2. Over in .NET, when you try to display a float column in a
DataGridView, it truncates trailing zeros, but when you display a
decimal column, you get trailing zeros galore. I would be more than
happy to manually set the format of any decimal column in my
DataGridView to "act like you're displaying a float even though you're
not," but I have yet to figure out how.
Of course, I could write some custom code to make the remaining digits
appear, but, besides being a pain in the ass, if all of a sudden 13
trailing zeros appear when the user goes into edit mode, that's not
really a good thing for usability, etc.
That is debatable, I suppose. If the users are going to be editing the 13th
decimal place when it is non-zero, it is arguable that they aren't going to
complain much to see digits there when they are simply zeros.
Let's go back to what the user is trying to do. I'm trying to enter a
latitude, say, 25.5 degrees. I type in 25.4. Later I realize, oops,
that should have been 25.5. I go in to edit the cell.
The cell now contains 25.400000000000000, but all I see is 00000000,
because the column is not wide enough to display all the digits. Now I
have to put the cursor in the cell, hold down left arrow (or try to
nudge it with the mouse) until I can once again see the 4. Then I need
to carefully select just the 4 and type a 5.
If the cell simply contained 25.4, I could just place cursor to the
right of the 4, hit backspace, type 5. Done.
So, for this scenario at least, I see the usability difference as quite
non-debatable.
In any case,
SQL Server is not in a position to magically fix this problem for you, nor
would I expect it to be.
It's really an interaction between SQL Server and .NET, so I'm not
saying it's SQL Server's "fault," but I can't see how it would *hurt*
to have something like a vardecimal.
There are four combinations of things a programmer may need:
1. Floating decimal point/no trailing zeros + faithful base-10
storage/retrieval
2. Floating decimal point/no trailing zeros + efficient
storage/processing
3. Fixed decimal point/mandatory trailing zeros + faithful base-10
storage/retrieval
4. Fixed decimal point/mandatory trailing zeros + efficient
storage/processing
Right now, I can only do 2 and 3, and I think that's a problem.
-Dan
Mike C# <xy*@xyz.comwrote:
<snip>
So, if you require data to be stored/retrieved via strings, it looks
like FLOAT/REAL may have problems due to a poor set of conversion
functions (or my ignorance, of course) - but you *can* store and
retrieve values exactly, via the BINARY type.
But that doesn't really do you any good, does it?
Yes it does - if I have a number which can be accurately represented as
a float, I can perform sufficient conversions to store it exactly as a
float, and retrieve it too.
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Mike C# <xy*@xyz.comwrote:
>
"Mike C#" <xy*@xyz.comwrote in message
news:u$**************@TK2MSFTNGP02.phx.gbl...
By the way, here's shorthand for the set of valid values for NUMERIC(38,
20):
The set of valid values for NUMERIC(38, 20) includes all decimal numbers
with 18 or fewer digits before the decimal point and 20 or fewer digits
after the decimal point.
Well, in that case I could (somewhat painstakingly) write a similar
description in terms of binary strings which are representable as
floats. It would be painstaking because of subnormals, but it's
certainly doable.
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Shuurai <Sh*******@hotmail.comwrote:
The benefit is that you're left with a useful definition. By your
reasoning, no datatype can ever be "exact" so the term is rendered
essentially useless. We can't ever store the "exact" value of 1/3 in
any meaningful way.
No, by my definition pretty much all datatypes are exact. (I can't
think of any that aren't, offhand.) Conversions between datatypes (at
least numeric ones) tend to be lossy.
Either way you render the definition useless. The way it's used in SQL
explains the behavior that can be expected.
.... at the cost of making it less clear what's actually going on, IMO.
Let's look at another example - INTEGER. Is INTEGER approximate? I
wouldn't say so - but if you tried to convert 28.08 into an INTEGER,
you'd certainly lose information, because that conversion is lossy.
Is INTEGER described as an "approximate" type by ANSI? I doubt it
somehow...
When you convert 28.02 into an integer you are storing it as 28. Not
an approximation, but exactly 28. The definition describes the
behavior of the data type.
When you convert 28.08 into a float, you are storing it as
28.0799999999999982946974341757595539093017578125. Not an
approximation, but exactly
28.0799999999999982946974341757595539093017578125
Now, that's certainly less intuitive than 28.08 -28, but it's still
an exact number.
So when there's no lossy conversion involved, there's no loss of
information, but when there *is* a lossy conversion involved,
information is lost. That's not exactly a surprise, and has nothing to
do with the types themselves, as far as I can see.
Exactly - but the definitions are there to describe to a user (like our
OP) who wants to know which data type will give him the same number he
intended to store.
Unfortunately, by doing so it loses sight of the fact that floats store
numbers exactly - they just can't store all decimal numbers exactly.
I don't see why people believe it's so difficult to explain/understand
that it's the conversion which is lossy, not the type.
It's not that it's difficult to explain or understand; it's just not
the intent of the defintion.
I believe that definitions should be *accurate*, and in my view (I'm
afraid no-one's changed my mind, and I don't expect I've changed anyone
else's mind) the definition *isn't* accurate.
Note that if you pass in floating binary point data rather than decimal
data, you *won't* lose information - because hey, again, there's no
conversion involved. That isn't clear if you regard the type itself as
being lossy, but it's absolutely obvious when you separate conversions
from types.
Note that the OP asked why he was getting a 28.080000000000002 instead
of 28.08 :)
Using the terminology common to SQL, we can tell him to use one of the
various data types described as "exact" rather than the "approximate"
data type he is using now. Simple, correct answer that solves his
problem immediately.
We could have told him that without giving the impression that the
float type is "approximate" though:
"When a decimal number is converted into a FLOAT, information can be
lost. It's a lossy conversion because not all decimal numbers can be
exactly represented as binary floating point numbers. Use the DECIMAL
type instead to keep the exact decimal value."
Or, we could give him the nitty-gritty details of converting base2
numbers to base10, which while certainly interesting isn't all that
helpful.
Does the above go into nitty gritty? Nope - but it doesn't give a
misleading impression either, IMO.
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Let's go back to what the user is trying to do. I'm trying to enter a
latitude, say, 25.5 degrees. I type in 25.4. Later I realize, oops,
that should have been 25.5. I go in to edit the cell.
How many times is the user going to enter 25.4672362763232 and want to edit
that?
A
"Daniel Manes" <da******@cox.netwrote in message
news:11*********************@o58g2000hsb.googlegro ups.com...
As the OP (!), I can tell you that, yes, this whole debate has been
interesting, but the helpful part is knowing which data type to use
when.
For the particular case at hand (trying to display lat/long values back
to the user exactly as originally entered), I decided to change the
data type of my lat/long columns in SQL to DECIMAL(18, 15). Note, more
precision than necessary for this application but just wanted to see
what happens.
The result: When displayed in a .NET DataGridView, every number
displayed had 15 decimal digits. So, a number orginally entered as
"23.5" showed up as "23.500000000000000." Not what I was hoping for.
Using floats, the DataGridView would display only as many digits as
necessary. With decimals, it shows them all, whether they add useful
information or not.
6 digits past the decimal point is accurate to about 4.4 inches in
geocoding. Five digits past the decimal point is about 3.6 feet of
accuracy. Unless you're trying to use a spy satellite to read license
plates, 5 places past the decimal point should be plenty.
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft.c om...
Mike C# <xy*@xyz.comwrote:
>>
"Mike C#" <xy*@xyz.comwrote in message
news:u$**************@TK2MSFTNGP02.phx.gbl...
By the way, here's shorthand for the set of valid values for NUMERIC(38,
20):
The set of valid values for NUMERIC(38, 20) includes all decimal numbers
with 18 or fewer digits before the decimal point and 20 or fewer digits
after the decimal point.
Well, in that case I could (somewhat painstakingly) write a similar
description in terms of binary strings which are representable as
floats. It would be painstaking because of subnormals, but it's
certainly doable.
I can't wait to read it.
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft.c om...
Mike C# <xy*@xyz.comwrote:
<snip>
So, if you require data to be stored/retrieved via strings, it looks
like FLOAT/REAL may have problems due to a poor set of conversion
functions (or my ignorance, of course) - but you *can* store and
retrieve values exactly, via the BINARY type.
But that doesn't really do you any good, does it?
Yes it does - if I have a number which can be accurately represented as
a float, I can perform sufficient conversions to store it exactly as a
float, and retrieve it too.
If you have a number which can be accurately represented as a float, then
you don't need to perform "sufficient conversions" and store/retrieve the
values via BINARY type. All that functionality is built in to the FLOAT
type as it stands. So again I ask, what good does it do you to store and
retrieve floating point decimal values as BINARY types?
BTW, I'm still waiting for you definition of "exact" types...
---
"Exact data types are those data types which can store and retrieve single
values coincident with their type without loss of data, and without the
introduction of error."
Mike C# <xy*@xyz.comwrote:
Well, in that case I could (somewhat painstakingly) write a similar
description in terms of binary strings which are representable as
floats. It would be painstaking because of subnormals, but it's
certainly doable.
I can't wait to read it.
Frankly, I don't have the energy. I don't know whether you genuinely
don't believe it's possible or whether you really want me to waste my
time on it. There are plenty of descriptions of how floats are stored,
and it's far from difficult to reverse engineer that to work out what
*can* be stored.
Why would it possibly be worth my time writing it up?
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Aaron Bertrand [SQL Server MVP] wrote:
Let's go back to what the user is trying to do. I'm trying to enter a
latitude, say, 25.5 degrees. I type in 25.4. Later I realize, oops,
that should have been 25.5. I go in to edit the cell.
How many times is the user going to enter 25.4672362763232 and want to edit
that?
If you're trying to say my example was an exaggeration, fine. Sometimes
people do that to make a point. All I'm saying is that trailing zeros
are sometimes a nuisance, and it would be nice if there were an easy
way to get rid of them without losing any data in the process.
-Dan
If you're trying to say my example was an exaggeration, fine. Sometimes
people do that to make a point. All I'm saying is that trailing zeros
are sometimes a nuisance, and it would be nice if there were an easy
way to get rid of them without losing any data in the process.
I'm sure in C# you could work out a way to format the data as a string,
removing trailing zeros but keeping all relevant decimal places.
A
Mike C# wrote:
6 digits past the decimal point is accurate to about 4.4 inches in
geocoding. Five digits past the decimal point is about 3.6 feet of
accuracy. Unless you're trying to use a spy satellite to read license
plates, 5 places past the decimal point should be plenty.
Thanks, Mike,
That's exactly what I was just trying to calculate. I was figuring
accurate to a yard would be plenty and was just going to base my
decimal digits on that. I did my calculations a little differently,
though, since users can enter in either degrees or degs-mins-secs. I
somehow came up with ~two feet per hundredth of a second (centisecond?)
of arc. Which is equivalent to 0.0000028 degrees. Either way, looks
like decimal(9, 6) ought to be enough, even for detecting relatively
small movements.
-Dan
Rule of thumb is:
For decimal places of decimal degrees of longitude at the equator:
3rd decimal place is sub-Kilometer
4th decimal place is sub-Hectometer
5th decimal place is sub-Decameter
6th decimal place is sub-meter
7th decimal place is sub-centimeter
8th decimal place is sub-millimeter
"Daniel Manes" <da******@cox.netwrote in message
news:11**********************@i39g2000hsf.googlegr oups.com...
Mike C# wrote:
>6 digits past the decimal point is accurate to about 4.4 inches in
geocoding. Five digits past the decimal point is about 3.6 feet of
accuracy. Unless you're trying to use a spy satellite to read license
plates, 5 places past the decimal point should be plenty.
Thanks, Mike,
That's exactly what I was just trying to calculate. I was figuring
accurate to a yard would be plenty and was just going to base my
decimal digits on that. I did my calculations a little differently,
though, since users can enter in either degrees or degs-mins-secs. I
somehow came up with ~two feet per hundredth of a second (centisecond?)
of arc. Which is equivalent to 0.0000028 degrees. Either way, looks
like decimal(9, 6) ought to be enough, even for detecting relatively
small movements.
-Dan
Sorry! Correction.
Rule of thumb is:
For decimal places of decimal degrees of longitude at the equator:
1st decimal place is sub-Kilometer
2nd decimal place is sub-Hectometer
3rd decimal place is sub-Decameter
4th decimal place is sub-meter
5th decimal place is sub-decimeter
6th decimal place is sub-centimeter
7th decimal place is sub-millimeter
"Stephany Young" <noone@localhostwrote in message
news:%2*****************@TK2MSFTNGP06.phx.gbl...
Rule of thumb is:
For decimal places of decimal degrees of longitude at the equator:
3rd decimal place is sub-Kilometer
4th decimal place is sub-Hectometer
5th decimal place is sub-Decameter
6th decimal place is sub-meter
7th decimal place is sub-centimeter
8th decimal place is sub-millimeter
"Daniel Manes" <da******@cox.netwrote in message
news:11**********************@i39g2000hsf.googlegr oups.com...
>Mike C# wrote:
>>6 digits past the decimal point is accurate to about 4.4 inches in
geocoding. Five digits past the decimal point is about 3.6 feet of
accuracy. Unless you're trying to use a spy satellite to read license
plates, 5 places past the decimal point should be plenty.
Thanks, Mike,
That's exactly what I was just trying to calculate. I was figuring
accurate to a yard would be plenty and was just going to base my
decimal digits on that. I did my calculations a little differently,
though, since users can enter in either degrees or degs-mins-secs. I
somehow came up with ~two feet per hundredth of a second (centisecond?)
of arc. Which is equivalent to 0.0000028 degrees. Either way, looks
like decimal(9, 6) ought to be enough, even for detecting relatively
small movements.
-Dan
Mike C# <xy*@xyz.comwrote:
But that doesn't really do you any good, does it?
Yes it does - if I have a number which can be accurately represented as
a float, I can perform sufficient conversions to store it exactly as a
float, and retrieve it too.
If you have a number which can be accurately represented as a float, then
you don't need to perform "sufficient conversions" and store/retrieve the
values via BINARY type. All that functionality is built in to the FLOAT
type as it stands. So again I ask, what good does it do you to store and
retrieve floating point decimal values as BINARY types?
Well, the only way I was able to get the *exact* value out of a float
so that I could then convert it (in C#) into a double was to use
binary. I suspect if I'd used C# to retrieve the value in the first
place (rather than just Query Analyzer) I would have got the right
answer that way - it's just a shame that you can't (as far as I can
tell) get SQL Server itself to tell you the exact value of the float.
BTW, I'm still waiting for you definition of "exact" types...
I wouldn't choose to use the words "exact" or "approximate" in the
first place.
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jon Skeet [C# MVP] (sk***@pobox.com) writes:
Either 28.08 shouldn't be regarded as being "in the range of valid
values" for FLOAT, *or* it should be regarded as being "in the range of
valid values" for DECIMAL(1,2). I'm happy to use either definition, so
long as it's used consistently.
The entire point you seem to be missing in this thread is what we use
types for. No on would use an int to store 28.02, but we may want to
use float to that aim. But it is not possible to store 28.02 exactly in
a float, so we get an approximation of the value we are really thinking of.
That is why float/real are approxamite, and decimal and int are not. They
are approximations of what we really want to store.
Yes, it is true that it is possible to store some values with very many
decimals exactly in a float, but that is completely irrelevant, because
floats are very rarely if ever used for that aim.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog <es****@sommarskog.sewrote:
Jon Skeet [C# MVP] (sk***@pobox.com) writes:
Either 28.08 shouldn't be regarded as being "in the range of valid
values" for FLOAT, *or* it should be regarded as being "in the range of
valid values" for DECIMAL(1,2). I'm happy to use either definition, so
long as it's used consistently.
The entire point you seem to be missing in this thread is what we use
types for. No on would use an int to store 28.02, but we may want to
use float to that aim. But it is not possible to store 28.02 exactly in
a float, so we get an approximation of the value we are really thinking of.
I've never disputed that. I've never disputed the use of decimal as a
way of storing decimal numbers exactly. I've said several times that I
absolutely agree with the use of decimal for the OP's problem. I'm not
concerned about the *use* of the type - we can all agree on that. How
you use something doesn't, to my mind, define what it *is*. Most of the
32-bit integers I use in .NET will never actually have numbers above
1000 in them, but that doesn't mean that the type itself can't store
anything above 1000 - what it can and can't store is precisely defined
regardless of how I choose to use it.
My concern is that float is being arbitrarily labeled as "approximate"
just because it can't store all values of one particular base, despite
the fact that decimal can't store all values of other bases. Yes, base
10 is obviously the most widely used base, but it *is* just a base.
We're lucky that it's a multiple of 2 - otherwise exact float values
wouldn't be exactly representable in decimal...
Any float value is exact in and of itself. If it's an approximation to
some other value it was originally converted from, so be it - that
doesn't, to my mind, make the type itself "approximate".
That is why float/real are approxamite, and decimal and int are not. They
are approximations of what we really want to store.
The value in the float is only approximate equal to the decimal value
which was originally converted, but in itself it is an exact number.
Given a floating point value, there is a well-defined, precise number
that value represents.
Yes, it is true that it is possible to store some values with very many
decimals exactly in a float, but that is completely irrelevant, because
floats are very rarely if ever used for that aim.
Frankly, how many *decimals* a float stores is irrelevant to me. What
I'm interested in is whether the value stored in a float can be
regarded as "exact" or merely "approximate". Given a float
representing, say, 1.0101, that is an exact value.
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
"Daniel Manes" <da******@cox.netwrote in message
news:11**********************@i39g2000hsf.googlegr oups.com...
That's exactly what I was just trying to calculate. I was figuring
accurate to a yard would be plenty and was just going to base my
decimal digits on that. I did my calculations a little differently,
though, since users can enter in either degrees or degs-mins-secs. I
somehow came up with ~two feet per hundredth of a second (centisecond?)
of arc. Which is equivalent to 0.0000028 degrees. Either way, looks
like decimal(9, 6) ought to be enough, even for detecting relatively
small movements.
There's a blog entry over at geocoder.us where he breaks down the digits of
precision in a table format: http://geocoder.us/blog/2006/03/23/h...ts-are-enough/. His table
has decimals, degrees, statute miles, feet and inches. For East-West
distances it's different (since the distance between lines of longitude gets
shorter as you move away from the equator), but it's pretty close to the
same thing over the continental U.S. If you're geocoding much farther north
or far south of the equator, you will probably need to make adjustments. At
any rate, 6 digits past the decimal point should be adequate regardless of
where you're geocoding.
Jon Skeet [ C# MVP ] wrote:
Shuurai <Sh*******@hotmail.comwrote:
The benefit is that you're left with a useful definition. By your
reasoning, no datatype can ever be "exact" so the term is rendered
essentially useless. We can't ever store the "exact" value of 1/3 in
any meaningful way.
>
No, by my definition pretty much all datatypes are exact. (I can't
think of any that aren't, offhand.) Conversions between datatypes (at
least numeric ones) tend to be lossy.
Either way you render the definition useless. The way it's used in SQL
explains the behavior that can be expected.
... at the cost of making it less clear what's actually going on, IMO.
While making it more clear what can be expected as a result. Those of
us who know how it's working don't need the definition as much as
someone who's just looking to get the same number out that they put in.
Let's look at another example - INTEGER. Is INTEGER approximate? I
wouldn't say so - but if you tried to convert 28.08 into an INTEGER,
you'd certainly lose information, because that conversion is lossy.
Is INTEGER described as an "approximate" type by ANSI? I doubt it
somehow...
When you convert 28.02 into an integer you are storing it as 28. Not
an approximation, but exactly 28. The definition describes the
behavior of the data type.
When you convert 28.08 into a float, you are storing it as
28.0799999999999982946974341757595539093017578125. Not an
approximation, but exactly
28.0799999999999982946974341757595539093017578125
And you'll note that 28.0 !=
28.0799999999999982946974341757595539093017578125
Now, that's certainly less intuitive than 28.08 -28, but it's still
an exact number.
Yes, it's an exact number. It's just not the exact number that we
stored.
So when there's no lossy conversion involved, there's no loss of
information, but when there *is* a lossy conversion involved,
information is lost. That's not exactly a surprise, and has nothing to
do with the types themselves, as far as I can see.
Exactly - but the definitions are there to describe to a user (like our
OP) who wants to know which data type will give him the same number he
intended to store.
Unfortunately, by doing so it loses sight of the fact that floats store
numbers exactly - they just can't store all decimal numbers exactly.
So your argument is that they store SOME numbers exactly?
I don't see why people believe it's so difficult to explain/understand
that it's the conversion which is lossy, not the type.
It's not that it's difficult to explain or understand; it's just not
the intent of the defintion.
I believe that definitions should be *accurate*, and in my view (I'm
afraid no-one's changed my mind, and I don't expect I've changed anyone
else's mind) the definition *isn't* accurate.
Definitions need to have a balance between usefulness and accuracy.
Your definition, even if technically accurate (which I don't believe it
is) just isn't useful.
Note that if you pass in floating binary point data rather than decimal
data, you *won't* lose information - because hey, again, there's no
conversion involved. That isn't clear if you regard the type itself as
being lossy, but it's absolutely obvious when you separate conversions
from types.
Note that the OP asked why he was getting a 28.080000000000002 instead
of 28.08 :)
Using the terminology common to SQL, we can tell him to use one of the
various data types described as "exact" rather than the "approximate"
data type he is using now. Simple, correct answer that solves his
problem immediately.
We could have told him that without giving the impression that the
float type is "approximate" though:
"When a decimal number is converted into a FLOAT, information can be
lost. It's a lossy conversion because not all decimal numbers can be
exactly represented as binary floating point numbers. Use the DECIMAL
type instead to keep the exact decimal value."
Which is absolutely correct. The definition is used to describe what
can be expected of the data type. It doesn't address (or need to
address) anything that is going on in the background. The point of the
definition is to tell the user which types will store 28.08 as 28.08.
Or, we could give him the nitty-gritty details of converting base2
numbers to base10, which while certainly interesting isn't all that
helpful.
Does the above go into nitty gritty? Nope - but it doesn't give a
misleading impression either, IMO.
Not misleading; just not particularly useful.
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft.c om...
Well, the only way I was able to get the *exact* value out of a float
so that I could then convert it (in C#) into a double was to use
binary. I suspect if I'd used C# to retrieve the value in the first
place (rather than just Query Analyzer) I would have got the right
answer that way - it's just a shame that you can't (as far as I can
tell) get SQL Server itself to tell you the exact value of the float.
Yet even with C# you can't always retrieve the *exact* value you originally
assigned. You retrieve an approximation of that value... You put 28.08
into the bucket and pull out 28.079999923706055, which is not the "right
answer".
I wouldn't choose to use the words "exact" or "approximate" in the
first place.
<Message Author="Jon Skeet [C# MVP]">
"No, by my definition pretty much all datatypes are exact."
</Message>
I'm still waiting to hear your definition, but only because you indicated
that you actually have one.
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP***********************@msnews.microsoft.co m...
Frankly, I don't have the energy. I don't know whether you genuinely
don't believe it's possible or whether you really want me to waste my
time on it. There are plenty of descriptions of how floats are stored,
and it's far from difficult to reverse engineer that to work out what
*can* be stored.
Why would it possibly be worth my time writing it up?
<Message Author = "Jon Skeet [C# MVP]">
"Well, in that case I could (somewhat painstakingly) write a similar
description in terms of binary strings which are representable as floats."
</Message>
....
<Message Author = "Jon Skeet [C# MVP]">
"No, by my definition pretty much all datatypes are exact. "
</Message>
....
<Message Author = "Jon Skeet [C# MVP]">
"I'll do that (publish a complete list of valid values for the FLOAT and
REAL types) if you publish a complete list of valid values for
NUMERIC(38,20)."
</Message>
I'm only interested in your definitions and descriptions because you
indicate that you either have them on hand, or are able to produce them. If
you don't have any definitions and/or can't produce what you said you can,
then I'd say the whole issue is fairly well resolved. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
79 posts
views
Thread by Daniel Manes |
last post: by
| | | | | | | | | | | | | | | | | | | |
