|
|
You're in the
sql server programming
group:inserting single precision data into sql server float column
sql server programming:
Hi,
iam using the bcp api to load data into sql server. The data to be loaded is single precision and hence my bcp_bind type is SQLFLT4. The column in my sql server table is a FLOAT(which is of course double precision). If i try to insert say a value 73.22 it gets inserted as 73.22000122070313. I mean the documentation says that implicit conversion for these types are allowed. So iam not sure why this happens. Appreciate any inputs.
Hi Vivek,
Thats the way float works: "Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. " DECLARE @SOMEValue Float(2) SEt @SomeValue = 1.100001 SELECT @SOMEValue For more precicion you have to use another database like decimal. HTH, Jens Suessmeyer.
I think my query was not stated clearly. If i load the same value into a REAL column it shows exactly what i inserted. (73.22) Similarily if i store that value in a double precision program variable and load into a FLOAT column it shows exactly what i stored. The problem is when the value is in a single precision program variable and i load into a FLOAT [quoted text, click to view] "Mike Hodgson" wrote:
> Because a float in SQL Server is an *approximate* floating point > representation, essentially meaning if you round it to the appropriate > number of significant digits then you'll get the number you're after but > it's only stored as accurately as the binary numbering system can manage > (defined by IEEE 754). The same would happen if you used real rather > than float. I think what you're after is an *exact* floating point > representation, which corresponds to the numeric (or decimal) data types > in SQL Server (i.e. fixed precision & scale). > > See Using decimal, float and real data > <http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_6mht.asp> in > SQL Books Online. > > -- > *mike hodgson* > http://sqlnerd.blogspot.com > > > > Vivek wrote: > > >Hi, > > iam using the bcp api to load data into sql server. The data to be loaded > >is single precision and hence my bcp_bind type is SQLFLT4. The column in my > >sql server table is a FLOAT(which is of course double precision). > >If i try to insert say a value 73.22 it gets inserted as 73.22000122070313. > >I mean the documentation says that implicit conversion for these types are > >allowed. So iam not sure why this happens. > >Appreciate any inputs. > > > >Vivek > > > >
[quoted text, click to view]
> I think my query was not stated clearly. If i load the same value into a > REAL column it shows exactly what i inserted. (73.22) We understood the question. You do not understand the issue. Did you read BOL regarding their definition and usage (Accessing and Changing Relational Data / Using decimal, float, and real Data)? If so, then contine with http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse representation of a value with the actual value. What you claim to see can also be an artifact of whatever technique you use to "see" the value after storage in the database. Below is a script that demonstrates the problem more clearly. set nocount on declare @test1 real, @test2 float, @test3 float(2) set @test1 = 73.22 set @test2 = 73.22 set @test3 = 73.22 select @test1, @test2, @test3 select cast(@test1 as varbinary(8)), cast(@test2 as varbinary(8)), cast(@test3 as varbinary(8)) print @test1 print @test2 print @test3
Because a float in SQL Server is an *approximate* floating point
representation, essentially meaning if you round it to the appropriate number of significant digits then you'll get the number you're after but it's only stored as accurately as the binary numbering system can manage (defined by IEEE 754). The same would happen if you used real rather than float. I think what you're after is an *exact* floating point representation, which corresponds to the numeric (or decimal) data types in SQL Server (i.e. fixed precision & scale). See Using decimal, float and real data <http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_6mht.asp> in SQL Books Online. -- *mike hodgson* http://sqlnerd.blogspot.com [quoted text, click to view] Vivek wrote:
>Hi, > iam using the bcp api to load data into sql server. The data to be loaded >is single precision and hence my bcp_bind type is SQLFLT4. The column in my >sql server table is a FLOAT(which is of course double precision). >If i try to insert say a value 73.22 it gets inserted as 73.22000122070313. >I mean the documentation says that implicit conversion for these types are >allowed. So iam not sure why this happens. >Appreciate any inputs. > >Vivek >
That's what i thought initially. (I use Query Analyzer btw) After inserting the same value (from a double precision and single precision variable respectively) into a FLOAT column, if i do a 'select * from tfloat' i get this output: 73.22 73.22000122070313 Would you say that the actual values of both rows are the same regardless of what i see above? The binary values are: 0x40524E147AE147AE 0x40524E1480000000 [quoted text, click to view] "Scott Morris" wrote:
> > I think my query was not stated clearly. If i load the same value into a > > REAL column it shows exactly what i inserted. (73.22) > > We understood the question. You do not understand the issue. Did you read > BOL regarding their definition and usage (Accessing and Changing Relational > Data / Using decimal, float, and real Data)? If so, then contine with > http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse > representation of a value with the actual value. What you claim to see can > also be an artifact of whatever technique you use to "see" the value after > storage in the database. Below is a script that demonstrates the problem > more clearly. > > set nocount on > declare @test1 real, @test2 float, @test3 float(2) > > set @test1 = 73.22 > set @test2 = 73.22 > set @test3 = 73.22 > > select @test1, @test2, @test3 > select cast(@test1 as varbinary(8)), cast(@test2 as varbinary(8)), > cast(@test3 as varbinary(8)) > print @test1 > print @test2 > print @test3 > >
Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
insertions and stick to single to single and double to double precision insertions? [quoted text, click to view] "Hugo Kornelis" wrote:
> On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote: > > > > >That's what i thought initially. (I use Query Analyzer btw) After inserting > >the same value (from a double precision and single precision variable > >respectively) into a FLOAT column, if i do a 'select * from tfloat' i get > >this output: > > > >73.22 > >73.22000122070313 > > > >Would you say that the actual values of both rows are the same regardless of > >what i see above? > >The binary values are: > > > >0x40524E147AE147AE > >0x40524E1480000000 > > Hi Vivek, > > These binary values explainexactly what's going on. > > The closest representation in a double precision representation is, > obviosuly, 0x40524E147AE147AE. When you store that in a single precision > variable or column, it has to be rounded to the closest that can be > represented in the 24 bits set aside for single precision, which is > apparently 0x40524E148. If you then store this in a double precision > column, the extra bits are added again - but of course as 0 bits, since > SQL Server has no memory of the bits that were prreviously lost. And so > it ends up as 0x40524E1480000000. > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote: > >That's what i thought initially. (I use Query Analyzer btw) After inserting >the same value (from a double precision and single precision variable >respectively) into a FLOAT column, if i do a 'select * from tfloat' i get >this output: > >73.22 >73.22000122070313 > >Would you say that the actual values of both rows are the same regardless of >what i see above? >The binary values are: > >0x40524E147AE147AE >0x40524E1480000000 Hi Vivek, These binary values explainexactly what's going on. The closest representation in a double precision representation is, obviosuly, 0x40524E147AE147AE. When you store that in a single precision variable or column, it has to be rounded to the closest that can be represented in the 24 bits set aside for single precision, which is apparently 0x40524E148. If you then store this in a double precision column, the extra bits are added again - but of course as 0 bits, since SQL Server has no memory of the bits that were prreviously lost. And so it ends up as 0x40524E1480000000. --
[quoted text, click to view]
On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote: >Thanks Hugo. That sounds reasonable. So do i just avoid these kind of >insertions and stick to single to single and double to double precision >insertions? Hi Vivek, I don't know what the requirements of your applications are. But as a rule of thumb, I'd recommend to avoid conversions as much as possible, stick to the same precision. Once you've lost precision, there's no way to get it back. But OTOH, storing data at more than required precision is just a waste of space. Find the precision you need, then design your DB and application around that. --
Thank you guys.
[quoted text, click to view] "Hugo Kornelis" wrote:
> On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote: > > >Thanks Hugo. That sounds reasonable. So do i just avoid these kind of > >insertions and stick to single to single and double to double precision > >insertions? > > Hi Vivek, > > I don't know what the requirements of your applications are. But as a > rule of thumb, I'd recommend to avoid conversions as much as possible, > stick to the same precision. Once you've lost precision, there's no way > to get it back. But OTOH, storing data at more than required precision > is just a waste of space. > > Find the precision you need, then design your DB and application around > that. > > -- > Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |