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 2E15).
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 6668
"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
How
you use something doesn't, to my mind, define what it *is*.
By that logic, a computer that hosts applications on a network shouldn't be
called a "server" and a computer that connects to it shouldn't be called a
"client", since they're all "computers" anyway and how you use them is
irrelevant to what they are? Some people might claim that how you use
something might be very closely tied to how we classify it.
32bit 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.
However when you store 989 in it, you are certain to get back 989. Not 988
or 990 "approximations " of 989.
My concern is that float is being arbitrarily labeled as "approximat e"
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.
Base 10 is what we use in SQL for floating point numbers, most likely
because no one has introduced a better system. Perhaps you should recommend
Base 3 to ANSI, or recommend that people work out their own binary
representations of floating point values and store them directly using the
BINARY data type?
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...
I thought your argument was that Base was irrelevant? Now you have a bias
for Base 2? What about Base 3?
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 "approximat e".
And in the case of 28.08, the float that is stored is *exactly* the wrong
number.
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 welldefined, precise number
that value represents.
And in SQL we tend to concern ourselves with little things like data
integrity; i.e., am I going to get out what I put in? Or am I going to get
back something different?
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 "approximat e". Given a float
representing, say, 1.0101, that is an exact value.
The fact that you can't store many values that are coincident to the data
type exactly without data loss or introducing error (i.e., "approximation" )
makes them "approximat e".
"Shuurai" <Sh*******@hotm ail.comwrote in message
news:11******** **************@ 51g2000cwl.goog legroups.com...
So your argument is that they store SOME numbers exactly?
From his posts so far his argument seems to boil down to "all data types are
stored as bits which are either *exactly* on or *exactly* off".
Of course by that reasoning you could create a data type that completely
disregards anything you put in and stores something entirely different, but
it would be just as *exact* as any other data type. It would be interesting
(though admittedly pointless) to have a SURPRISE data type where you could
put in "28.08" and retrieve "A suffusion of yellow", "999999.9283746 ", "c^2
= a^2 + b^2", "3.141592", or "9i + 12"; depending on what the computer
decided to store in it at any given moment. But since bits are exactly
either on or off, this would be another "exact" data type.
Another one of those "It's technically accurate, but completely useless"
type things.
Erland Sommarskog <es****@sommars kog.sewrote:
Jon Skeet [C# MVP] (sk***@pobox.co m) writes:
My concern is that float is being arbitrarily labeled as "approximat e"
No, it is not arbitrary. It relates to what humans would like to be
able to store, but which is not always possible. The fact that the floating
number actually belongs to the set of decimal numbers is completely
rrelevant when it comes to usage.
In many cases, it's what humans want to store  but not always. What
about if I'm storing some data in the database when it's been computed
(or measured) using binary floating point to start with? At that point,
assuming you've got the right precision, you will lose no data at all.

Jon Skeet  <sk***@pobox.co m> 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.comwro te:
"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
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.079999923706 055, which is not the "right
answer".
You can retrieve the exact value which was actually assigned, but not
the "preconversion" value. That's only to be expected with a lossy
conversion.
I wouldn't choose to use the words "exact" or "approximat e" 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.
If I were forced into one, I'd say your definition of exact (which I
believe includes float) is pretty reasonable.

Jon Skeet  <sk***@pobox.co m> 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*******@hotm ail.comwrote:
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.
Well, I don't think it makes it any clearer, but that's clearly a
matter of opinion.
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.079999999999 998294697434175 759553909301757 8125. Not an
approximation, but exactly
28.079999999999 998294697434175 759553909301757 8125
And you'll note that 28.0 !=
28.079999999999 998294697434175 759553909301757 8125
(I assume you meant 28.08 rather than 28.0?)
Yes, just as 28 != 28.08 in your integer example. Just as with integer
you are storing exactly 28 (i.e. the closest integer to 28.08 with
appropriate rounding behaviour), with float you're storing exactly the
closest float to 28.08 with the specified rounding behaviour.
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.
Well, it's the exact number that you *stored*, just not the exact
number which was the source of the conversion.
My point is that the number stored in the float example is just as
exact as the number stored in the integer example. Both types have
rules for converting an arbitrary number to an exact value within the
set of representable values for that type. Where do you see the
difference that makes integer exact and float approximate?
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?
All types available only store *some* numbers exactly. Decimal doesn't
store all numbers exactly: "a third" is a number, but it can't be
stored exactly. Be careful to distinguish between "decimal numbers" and
"numbers".
I believe that definitions should be *accurate*, and in my view (I'm
afraid noone'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.
Unsurprisingly, I disagree. I don't expect either of us to persuade the
other.
We could have told him that without giving the impression that the
float type is "approximat e" 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.
It's not clear to me what you're regarding as "absolutely correct". The
bit in quotes in my post was my suggested alternative to just saying
that float is an approximate type. If you agree that my text is
correct, and tells the user which type will store 28.08 as 28.08,
what's the disadvantage of using my text?
Or, we could give him the nittygritty 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.
What does it *not* address that would be useful?

Jon Skeet  <sk***@pobox.co m> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
>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.079999923706 055, which is not the "right answer".
You can retrieve the exact value which was actually assigned,
BUT not the value that was assigned *by the user.* And that's the only
value *the user* tends to care about.
A
My point is that the number stored in the float example is just as
exact as the number stored in the integer example. Both types have
rules for converting an arbitrary number to an exact value within the
set of representable values for that type. Where do you see the
difference that makes integer exact and float approximate?
Maybe it's the surprise factor.
Maybe it's that we are used to being able to supply *less* than the number
of acceptable significant digits, and not having the values change on us
after the fact.
Maybe it's because integers are simpler, and the rounding of 28.08 28 is
much more intuitive, clear, expected, and predictable. This is stuff we all
learned in grade school. I think it takes a much better understanding of
mathematics to draw the same conclusions from the approximation of 28.08 >
28.xxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxx
A
Mike C# <xy*@xyz.comwro te:
>
"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** @msnews.microso ft.com...
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.
Don't be absurd. It would take time to write down all the rules for
which binary strings are exactly representable as float due to
denormalisation etc, but I *hope* that from descriptions of the format
(eg in http://www.yoda.arachsys.com/csharp/floatingpoint.html) that
it's obvious that such a rule is *possible* to write down with effort.
If you accept that it's possible  that there is inherently such a
rule, even though it would take a while to write it out accurately 
then you should accept that float falls within your definition of an
exact type. What good would having the actual rule do?
Now, do you actually believe it's not possible to write down the rule
which says which values are allowed? (Heck, I've given you a program
which would generate them all given long enough  so the theoretical
rule could be "if it appears in this list"; the list clearly exists,
even if it takes a long time to produce.)

Jon Skeet  <sk***@pobox.co m> 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.comwro te:
"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
How
you use something doesn't, to my mind, define what it *is*.
By that logic, a computer that hosts applications on a network shouldn't be
called a "server" and a computer that connects to it shouldn't be called a
"client", since they're all "computers" anyway and how you use them is
irrelevant to what they are? Some people might claim that how you use
something might be very closely tied to how we classify it.
That's a fair point, although I'd argue there is a difference here when
you talk about a type *being* approximate.
32bit 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.
However when you store 989 in it, you are certain to get back 989. Not 988
or 990 "approximations " of 989.
But if you store 989.1 in it, you won't get 989.1 back. No different to
float in that respect.
My concern is that float is being arbitrarily labeled as "approximat e"
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.
Base 10 is what we use in SQL for floating point numbers, most likely
because no one has introduced a better system. Perhaps you should recommend
Base 3 to ANSI, or recommend that people work out their own binary
representations of floating point values and store them directly using the
BINARY data type?
Well, communicating with SQL server from another program, if I were
storing or retrieving a float I almost certainly *wouldn't* use base 10
 I'd use base 2, as that's the "native" base of float within SQL
server and would almost certainly be the "native" base of my storage
format within the other program, too.
It's only SQL *literals* (and values of type decimal and numeric, of
course) which are explicitly base 10, isn't it?
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...
I thought your argument was that Base was irrelevant? Now you have a bias
for Base 2? What about Base 3?
No, I don't have a bias for base 2  I'm just saying that the fact that
we *can* represent all floats accurately as decimals comes from the
fact that 10 is divisible by 2. It's always nice when conversions *can*
be lossless.
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 "approximat e".
And in the case of 28.08, the float that is stored is *exactly* the wrong
number.
Yes. Just as if I try to store 28.08 as an integer, I'll be storing
exactly the wrong number (28), and just as if I try to store 1.234567
in a numeric (2,3) I'll be storing exactly the wrong number.
As we've already agreed, you can only expect a type to store numbers
within a particular set of available values. That's true for *all* the
types involved.
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 welldefined, precise number
that value represents.
And in SQL we tend to concern ourselves with little things like data
integrity; i.e., am I going to get out what I put in? Or am I going to get
back something different?
Which is why it's important for people to understand the difference
between float and decimal  but they can understand that without
believing that float doesn't store its values exactly. It just has a
different (but still welldefined) set of valid values.
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 "approximat e". Given a float
representing, say, 1.0101, that is an exact value.
The fact that you can't store many values that are coincident to the data
type exactly without data loss or introducing error (i.e., "approximation" )
makes them "approximat e".
No, you can store *all* values that are coincident to the data type.
You just can't store *any* values which *aren't* coincident to the data
type  which is the same for all other types as well.

Jon Skeet  <sk***@pobox.co m> 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] <te*****@dnartr eb.noraawrote:
My point is that the number stored in the float example is just as
exact as the number stored in the integer example. Both types have
rules for converting an arbitrary number to an exact value within the
set of representable values for that type. Where do you see the
difference that makes integer exact and float approximate?
Maybe it's the surprise factor.
Maybe it's that we are used to being able to supply *less* than the number
of acceptable significant digits, and not having the values change on us
after the fact.
FWIW, I've always thought that describing floating binary point types
as being accurate to a certain number of *decimal* digits is asking for
trouble.
Maybe it's because integers are simpler, and the rounding of 28.08 28 is
much more intuitive, clear, expected, and predictable. This is stuff we all
learned in grade school. I think it takes a much better understanding of
mathematics to draw the same conclusions from the approximation of 28.08 >
28.xxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxx
Yes, I dare say it takes a bit more understanding if you want to know
exactly why it stores the values it does.
I would even have been *somewhat* happy with an original answer (when I
started this whole business) of "yes, we know that float is exact
really, but explaining it as approximate is easier than bringing
conversions into the conversation". I would still have argued against
that, *but* the fact is that everyone's been arguing that float really
*is* inherently approximate, and from a mathematical standpoint which
doesn't have a base 10 bias I just can't accept that as being true.
Just because a conversion takes a bit more understanding doesn't mean
the value stored isn't exact.
All the types we've talked about (integer, decimal, float) have the
following in common:
1) They have a welldefined set of values they can exactly represent.
2) If you ask them to store a value which isn't in that set, they will
store (exactly) an approximation to that value. The rules for that
approximation are also welldefined.
3) If you ask them to store a value which *is* in that set, they will
store that value exactly.

Jon Skeet  <sk***@pobox.co m> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics 
by: Daniel Manes 
last post by:
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 2E15).
I'm not doing any operations on this column. It's just running a stored
procedure which performs a pretty basic...

by: marktang 
last post by:
ONU (Optical Network Unit) is one of the key components for providing highspeed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...

by: Hystou 
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

by: jinu1996 
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...

by: Hystou 
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
 
by: tracyyun 
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, ZWave, WiFi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

by: TSSRALBI 
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LANtoLAN VPNs.
The last exercise I practiced was to create a LANtoLAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

by: 6302768590 
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

by: muto222 
last post by:
How can i add a mobile payment intergratation into php mysql website.

by: bsmnconsultancy 
last post by:
In today's digital era, a welldesigned website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
 