March 20, 2011 at 2:03 am
I Have Table (Materials)
In this table there are column (Cost float)
I Update this table
Update Materials Set Cost = 0.37421
NOW:
When i read data i have this data: 0.374210000038147
Why ? ? ?
March 20, 2011 at 2:48 am
Attaf_T (3/20/2011)
I Have Table (Materials)In this table there are column (Cost float)
I Update this table
Update Materials Set Cost = 0.37421
NOW:
When i read data i have this data: 0.374210000038147
Why ? ? ?
your data type is float. Float isn't a precise data type.
If you would have used decimal(x,6) it would have stored exactly the data you inserted.
Search SSC and you'll find some articles or threads.
e.g. http://www.sqlservercentral.com/Forums/Topic524869-149-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 20, 2011 at 10:10 am
Thanks for your advice.
But I ask why sql Server do that?
Why sql dont save (0.123123 as 0.123123000000000000000)
and i must set Cost to float for my reasons
Is there way to prevent this additional numbers.
March 20, 2011 at 10:38 am
Attaf_T (3/20/2011)
Thanks for your advice.But I ask why sql Server do that?
Why sql dont save (0.123123 as 0.123123000000000000000)
Float is by definition an imprecise data type. It has to do with the binary representation used. Hence SQL cannot store it 100% precisely, that's what approximate means. If you use float then you are saying, by choosing that data type, that complete precision is not important. If it is absolutely essential that 0.123123 be stored exactly as 0.123123000000000000000 then you cannot use float (or real) and need to use decimal or numeric.
From Books Online:
float and real
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.
and
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.
The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply