Convert int to float

Hi,
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 lotWhat method are you using to convert?
How long is so long?

Your last sentence doesn't make a lot of sense - if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.

In fact, please post the SQL statement in question as well :)|||First of all, converting anything to float is a "computationally noisy" operation... It means a lot of work for the CPU no matter how you do it, just because of how the FLOAT datatype is stored.

My preference for doing what you've described is peculiar, but it works well for me... I would try using:SELECT 1e0 * inumber1 / inmuber2-PatP|||Thanks a lot Pat, that's a nice trick!|||What method are you using to convert?
How long is so long?

It was Alter Table A Alter Column C float NOT NULL and took about 20 Minutes.

if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.

That's why I did the Alter Command. I just wondered why its such a big thing from int to float|||for very large tables, it probably would be faster to export the table to a flat file using bcp, then reimport to a different table with the new datatype, again with bcp.

I tend to shy away from ALTER TABLE.|||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.|||Wow, that was a really detailed comment :-) thank you

As I already have converted, does it make any sense to reconvert to integer regarding the negative impact of greater magnitude?|||int to float

What the hell for?

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|||Have you ever divided two integer values?|||int to float

What the hell for?

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 pollI 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.|||Have you ever divided two integer values?You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?|||Correct me, if I'm wrong, but float takes less space than decimal, doesn't it?|||You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?
Not really, but 5M rows is still pretty small. Frankly, I thought one of you could give me that answer ;-)|||BOL

float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

Decimal

Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

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.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

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|||Queries should be a bit faster after that..

Ok, so this is you stated goal

supply us with DDL and the query that's "slow"

data type conversions are not where I would start for Perf Tuning|||exsqueeze me - shows my naivite of the subject. I think rather than floating point data types I should have said floating point calculations.

But in any event, if you want an accurate representation of the division of two integers a float is probably the wrong route.|||excuse me if I am wrong but I think one of the major drawbacks of floats is that you are not always guaranteed reproduceable results. I might be wrong about this.|||Ok - I found the link.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71391
It is really a display issue when you use a float. The problem is that float is internally a binary representation of the number, and the binary representation cannot always be converted directly to a decimal representation that is exactly equivalent. Float is intended for scientific applications where computational accuracy is the most important requirement.
My point was that Decimal is not always better (read more accurate) than Float. I probably should have made the point better & more succinctly though since in this case it is certainly better to use decimal.|||that's a great quote, pootle, thanks for following that up

better to use decimal? that depends on what kind of data you're storing

if it's money, yes :)

and pat will tell you it had better be DECIMAL(n,4), not 2 -- and then rounded by the front end if desired|||Have you ever divided two integer values?yup, and if i don't want a whole number answer, i use pat's trick, although mine is just slightly different:select 1.0 * col1 / col2:)