Why does 28.08 show up as 28.080000000000002 in DataGridView

[Barry Kelly]

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

(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

 

[Jon Skeet [C# MVP]]

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.

 

[Aaron Bertrand [SQL Server MVP]]

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

 

[Mike C#]

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?"

 

[Jon Skeet [C# MVP]]

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.

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...

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".

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...

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...

 

[Razvan Socol]

[...] 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

 

[Jon Skeet [C# MVP]]

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

 

[Mike C#]

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?

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.


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.

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.

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.

 

[Mike C#]

<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?

 

[Mike C#]

[...] 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.

 

[Shuurai]

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.

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.

 

[Mike C#]

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#]

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.

 

[Razvan Socol]

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.079999999999994741983755375258624553680419921875
(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.080000000000001847411112976260483264923095703125
(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

 

[Mike C#]

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.

 

[Daniel Manes]

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

 

[Razvan Socol]

[...] 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

 

[Aaron Bertrand [SQL Server MVP]]

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/2006/11/vardecimal.html

http://weblogs.sqlteam.com/mladenp/archive/2006/11/10/19546.aspx

http://blogs.msdn.com/sqlserverstor...s-for-enabling-vardecimal-storage-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.

 

[Daniel Manes]

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.

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

 

[Jon Skeet [C# MVP]]

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.