Real data type 2000 vs 2005

If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
In 2005, 56.26 is stored.
Does anyone know why?
Thx,
Don
That's just a difference in the way the particular user interface is
displaying the value. In both cases, the value that is actually stored if
you set a real variable or column to 56.26 is 56.259998321533203.
Real's cannot store every possible decimal value exactly, so SQL stores the
closest value that it can. Then it is up to the user interface to decide
how to display the value.
If you use the same user interface, (for example SQL 2000 QA) to query both
databases, you will get the same answer.
Tom
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:9807A147-795D-419B-825C-6002ADFB16A5@.microsoft.com...
> If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> In 2005, 56.26 is stored.
> Does anyone know why?
> Thx,
> Don
>
|||> If I insert 56.26 into a real data type in 2000, 56.259998 gets stored.
> In 2005, 56.26 is stored.
> Does anyone know why?
Because this isn't about storage, I think. When you insert the data in
2000, are you selecting the data using Query Analyzer to verify what is
"stored"? And when you insert the data in 2005, are you selecting the data
using Management Studio to verify what is "stored"? What changed is the
client, how they approximate, how many decimal places are relevant, and when
and where they can and should truncate. REAL/FLOAT are labeled as
"approximate" and if you want to avoid little surprises like how your client
tools display / translate the stored value, then you should explicitly cast
to a DECIMAL with the appropriate scale and precision, or use DECIMAL in the
first place.
|||If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
56.26.
But, the real value is 56.259998321533203. So, why can't I see the real
value? Is this a feature of the 2005 QA?
Don
"Tom Cooper" wrote:

> That's just a difference in the way the particular user interface is
> displaying the value. In both cases, the value that is actually stored if
> you set a real variable or column to 56.26 is 56.259998321533203.
> Real's cannot store every possible decimal value exactly, so SQL stores the
> closest value that it can. Then it is up to the user interface to decide
> how to display the value.
> If you use the same user interface, (for example SQL 2000 QA) to query both
> databases, you will get the same answer.
> Tom
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:9807A147-795D-419B-825C-6002ADFB16A5@.microsoft.com...
>
>
|||Try
SELECT CONVERT(FLOAT, column) FROM table;
or
SELECT CONVERT(DECIMAL(12,10), column) FROM table;
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:91993EF7-EDF1-4D17-B8F1-CB3B4D996F68@.microsoft.com...
> If I use QA from 2005 to query both the 2000 and the 2005 DB, the value is
> 56.26.
> But, the real value is 56.259998321533203. So, why can't I see the real
> value? Is this a feature of the 2005 QA?
|||In 2005, if I output from QA to a txt file, the value is 56.26.
If it's stored as 56.259998 then it seems that's what should be in the txt
file.
I'm thinking that 56.26 is stored in 2005.
Don
"Aaron Bertrand [SQL Server MVP]" wrote:

> Because this isn't about storage, I think. When you insert the data in
> 2000, are you selecting the data using Query Analyzer to verify what is
> "stored"? And when you insert the data in 2005, are you selecting the data
> using Management Studio to verify what is "stored"? What changed is the
> client, how they approximate, how many decimal places are relevant, and when
> and where they can and should truncate. REAL/FLOAT are labeled as
> "approximate" and if you want to avoid little surprises like how your client
> tools display / translate the stored value, then you should explicitly cast
> to a DECIMAL with the appropriate scale and precision, or use DECIMAL in the
> first place.
>
|||Interesting. The FLOAT statement shows me the stored value is
56.259998321533203
I'll have to think about this. Thanks.
When we convert to 2005, I don't know what will happen to our applications
that use SELECT statements without FLOAT. Will have to test.
Don
"Aaron Bertrand [SQL Server MVP]" wrote:

> Try
> SELECT CONVERT(FLOAT, column) FROM table;
> or
> SELECT CONVERT(DECIMAL(12,10), column) FROM table;
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:91993EF7-EDF1-4D17-B8F1-CB3B4D996F68@.microsoft.com...
>
|||> When we convert to 2005, I don't know what will happen to our applications
> that use SELECT statements without FLOAT. Will have to test.
Is there any overwhelming reason you chose FLOAT? If you switch to DECIMAL
(with the proper scale and precision), this problem will go away. Have you
even tested your app against ? The truncation/rounding you are seeing is a
*CLIENT TOOL DISPLAY* issue. Your app should continue to retrieve the
correct value... as I have demonstrated, the correct value is what is
stored, but you are stuck on the idea that SQL Server 2005 is ruining your
data. It is not, it is just displaying a prettier version of your data.
In any case, your applications shouldn't need to change at all, since they
should be calling stored procedures, right?
|||When I'm working in SQL 6.5 or 7.0 or 2000 over the years, when I'm in Query
Analyzer, the results window shows the stored data value.
Now, suddenly, in 2005, I don't get the stored value. It makes life difficult.
I don't want a prettier version of the data. I want the stored value to show
up in Query Analyzer.
Maybe there's a way to configure Query Analyzer to show the stored values as
all other versions of SQL did?
Don
"Aaron Bertrand [SQL Server MVP]" wrote:

> Is there any overwhelming reason you chose FLOAT? If you switch to DECIMAL
> (with the proper scale and precision), this problem will go away. Have you
> even tested your app against ? The truncation/rounding you are seeing is a
> *CLIENT TOOL DISPLAY* issue. Your app should continue to retrieve the
> correct value... as I have demonstrated, the correct value is what is
> stored, but you are stuck on the idea that SQL Server 2005 is ruining your
> data. It is not, it is just displaying a prettier version of your data.
> In any case, your applications shouldn't need to change at all, since they
> should be calling stored procedures, right?
>
|||> I don't want a prettier version of the data.
I know that. I was telling you what you are getting.

> Maybe there's a way to configure Query Analyzer to show the stored values
> as
> all other versions of SQL did?
I don't know of any such configuration option. To prevent confusion for
other readers, you will want to avoid using the term "Query Analyzer" if you
are describing the query window tool within SSMS. It is no longer called
Query Analyzer, so without context, people will think you are talking about
the 2000 version of the tool.
You didn't answer any of my other questions...