why does converting integer to float take so long? Its a column with about 5 Million rows.
I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so
Thanks a lot
As I see it, there is three main reasons for this operation to take take quite a bit of time:
First, creating the float value requires processing power. However, this does not explain how updating a table with 5M records takes so long.
Second, A float takes 8 bytes, whereas int takes 4 bytes. So, depending on the width of the table, you may experience page splits (There is no longer room for all the rows in the page, so some rows have to be moved to a new page). I'm pretty sure this will be especially bad if there is a clustered index on the table.
And last, if this is done in an active environment, keep in mind that you'll require exclusive schema lock on the table, so your command will have to wait until a point in time when there is no activity against the table.
So, finally... I don't think queries will run any faster. You'll probably end up having more data pages, and I'm pretty sure that the negative impact from this is of greater magnitude than the cast from int to float.
There's no precision in int. Are you looking for precision? Then use decimal.
Let me ask...does anyone use float or real, and if so what for?
I gotta do a poll
I think you mean "There's no precision in float". And a float is more precise when you do complex math (the sort of complex I don't really understand frankly). Michael Valentine Jones did a good proof on SQLTeam. I'm sure Pat could explain too. I've got a link to a page that goes into this in depth but have never had chance to read it yet.
EDIT - linky: http://docs.sun.com/source/806-3568/ncg_goldberg.html
Anyway - you are right though - for most purpses you would prefer decimal over floats.
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.
Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.
Integer data from 0 through 255. Storage size is 1 byte.
float [ ( n ) ]
Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
So How many bits in a byte...I think it's 8...but bol doesn't say what the default for float alone is
Looks like in EM in table design that float defaults to 8 bytes, so that would be half of what int is