August 29, 2013 at 4:18 pm
Ok, so this has no real bearing on my application but I'm curious. Given the following:
select len(0.28999999165535)
select len(cast(0.28999999165535 as float))
select 0.28999999165535 * 2
select cast(0.28999999165535 as float) * 2
The results are:
16
4
0.57999998331070
0.5799999833107
so..... WHY IS THE LEN OF THE FLOAT 4?! The reason I ask is that it's stored in the database (erroneously) as 0.28999999165535 instead of 0.29 but if I try to find it using a LEN() filter it doesn't filter as expected.
August 29, 2013 at 4:28 pm
WHY IS THE LEN OF THE FLOAT 4?!
It takes 4 bytes to store a float value (up to 24 digits).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2013 at 4:33 pm
So the len() function is counting bytes instead of digits? Is this particular to floats?
August 29, 2013 at 4:34 pm
From Books Online
float
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.
Hence, it's not stored erroneously. It's an approximate data type by definition.
If you want 0.29 to always be exactly 0.29, use a precise data type such as NUMERIC.
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
August 29, 2013 at 4:36 pm
Thanks, Gail, that part I understood. 😀 I was curious about the internal mechanics about the len() function not returning the number of digits in the float.
So if it takes 4 bytes to store up to 24 digits why does this
select len(cast(0.000001 as float))
give me a length of 6?
What is len() actually counting in a float?
August 29, 2013 at 4:39 pm
Erin Ramsay (8/29/2013)
So the len() function is counting bytes instead of digits? Is this particular to floats?
LEN takes a string as a parameter and counts the number of bytes in that string. It's not calculating the bytes required to store, that's what DATALENGTH does (and float takes 8 bytes to store)
So select len(cast(0.28999999165535 as float))
is actually
select len(cast(cast(0.28999999165535 as float) as varchar))
because LEN's parameter is a string, not any other data type
and
SELECT CAST(cast(0.28999999165535 as float) AS VARCHAR)
returns 0.29, a 4-character long string.
Hence LEN of that is 4.
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
August 29, 2013 at 4:41 pm
AHA!
Thanks, Gail! That's the piece I was missing. The implicit conversion to a string.
Sweet.. something new learned today...something I should have already known.. sigh..
August 29, 2013 at 4:42 pm
Erin Ramsay (8/29/2013)
Thanks, Gail, that part I understood. 😀 I was curious about the internal mechanics about the len() function not returning the number of digits in the float.So if it takes 4 bytes to store up to 24 digits
It doesn't. A float is 8 bytes unless you specify otherwise (FLOAT(24) is 4 bytes, but the default if not specified is FLOAT(53)) and LEN is not returning the storage size
why does this
select len(cast(0.000001 as float))give me a length of 6?
Because
SELECT CAST(cast(0.000001 as float)AS varchar)
returns
1e-006. 6 characters long.
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
August 29, 2013 at 4:43 pm
The reason that len(cast(0.28999999165535 as float)) returns a length of 4 is because the LEN() function accepts string or binary data which means an implicit conversion has to occur before the evaluation. And cast(cast(0.28999999165535 as float) as varchar) yields 0.29 which has 4 characters.
select cast(cast(0.28999999165535 as float) as varchar),
len(cast(0.28999999165535 as float)),
len(cast(cast(0.28999999165535 as float) as varchar))
Edit: Darn it! Gail types faster than me!
August 29, 2013 at 4:48 pm
Float only takes 4 bytes to store if you specify a precision of 24 or less.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2013 at 4:56 pm
ScottPletcher (8/29/2013)
Float only takes 4 bytes to store if you specify a precision of 24 or less.
When did you last see someone specifying the mantissa for a float? If not specified (as in the OP), the default is 53, which requires 8 bytes.
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
August 29, 2013 at 5:00 pm
GilaMonster (8/29/2013)
When did you last see someone specifying the mantissa for a float? If not specified (as in the OP), the default is 53, which requires 8 bytes.
I saw in some sample code from MSFT back in 2003.
August 29, 2013 at 5:01 pm
Robert Davis (8/29/2013)
GilaMonster (8/29/2013)
When did you last see someone specifying the mantissa for a float? If not specified (as in the OP), the default is 53, which requires 8 bytes.I saw in some sample code from MSFT back in 2003.
You're one up on me then, I've never seen float declared in any way other than the default.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply