December 21, 2004 at 1:53 am
I am facing a very strange problem
when i create table with a column float(8) a default (non-editable) precision of 53 is chosen by enterprise manager.
If I create the table through a script a default (non-editable) precision of 23 is chosen.
This is creating a strange problem. When I compare the values in the table created in different manner, I am not able to retrieve the records with the same data.
To better illustrate with an eg
Create a table table1 with column col1 float(8)
Now run the following script
insert into table1 values(7.0833333333299997)
declare @X float(8)
select @X=7.0833333333299997
select @X
select * from table1 tc where isnull(TC.col1,-1)= (isNull(@x,-1))
I am not getting the required row. This is leading to data not being shown in the required reports.
Please help
TIA
Rgds
Kudla
December 21, 2004 at 4:12 am
Have a look at the explanations in BOL for FLOAT. If you create the table in EM you specify only FLOAT. With a default precision of 53 the column takes 8 bytes. This is the 8 you see. However, when you create a table in QA and specify FLOAT(8), the is something quite different.
This works for me
create table #t1(col1 float)
insert into #t1 values(7.0833333333299997)
declare @X float
select @X=7.0833333333299997
--select @X
select * from #t1 tc where isnull(TC.col1,-1)= (isNull(@x,-1))
select * from #t1 where col1-@x = 0
drop table #t1
col1
-----------------------------------------------------
7.0833333333299997
(1 row(s) affected)
col1
-----------------------------------------------------
7.0833333333299997
(1 row(s) affected)
You are also aware of the fact that FLOAT and REAL are approximate datatypes?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 21, 2004 at 5:02 am
What I dont understand is the difference when the table is created through the enterprise manager and the Query Analyser.
What is the best possible solution for this problem. I have hundreds of stored procedures which have comparisons as mentioned above. Should I change the variable to float.
Thanks for the prompt reply...
Rgds
Kudla
December 21, 2004 at 6:18 am
Yes, I think simple FLOAT instead of FLOAT(8) should work.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 22, 2004 at 12:40 pm
I am interested as to what the float column is used for. Float is extremely imprecise. Adding columns of money amounts that are input to a float column will quickly be pennies away from the correct amount. Decimal or money datatypes are precise data types.
We found out in sql 6.0 to stay away from float.
December 22, 2004 at 2:15 pm
There is no black or white answer to this question. That really depends on what you want to do with these columns. For monetary units and calculations certainly DECIMAL is more appropriate. Even more than MONEY, which is highly proprietary and causes problems, when you do calculations other than addition or subtraction. Here's an example on FLOAT and DECIMAL:
declare @a decimal(18,4)
set @a = 0.0003
select
sum(a)*sum(a)*100,
100*sum(a)*sum(a)
from (
select @a a
union all
select @a
) x
go
declare @a float
set @a = 0.0003
select
sum(a)*sum(a)*100,
100*sum(a)*sum(a)
from (
select @a a
union all
select @a
) x
go
---------------------------------------- ----------------------------------------
.000000 .000036
(1 row(s) affected)
----------------------------------------------------- -----------------------------------------------------
3.5999999999999994E-5 3.5999999999999994E-5
(1 row(s) affected)
Which one is better? FLOAT? DECIMAL?
Here's one on MONEY and DECIMAL:
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
set @m1 = 1.00
set @m2 = 345.00
set @m3 = @m1/@m2
set @d1 = 1.00
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3
--------------------- ---------------------
.0028 .0029
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply