|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
inserting single precision data into sql server float columnHi,
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 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. 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. Show quote >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 > > 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 Show quote "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 > > > > > > I think my query was not stated clearly. If i load the same value into a We understood the question. You do not understand the issue. Did you read > REAL column it shows exactly what i inserted. (73.22) 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 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 Show quote "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 > > > On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
Show quote > Hi 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 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 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? Show quote "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 > On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>Thanks Hugo. That sounds reasonable. So do i just avoid these kind of Hi Vivek,>insertions and stick to single to single and double to double precision >insertions? 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 Thank you guys.
Show quote "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 >  |
|||||||||||||||||||||||
Other interesting topics