Does converting a float column to a bigint column result in a whole table copy in SQL Server?
we want to convert in a huge (100 Mio. rows) SQL Server table one column from FLOAT(8) to BIGINT. We do not care about the floating points and losing precision.
Since I do not own a SQL Server but are using mysql loca开发者_StackOverflow中文版lly I am wondering if this operation results in a whole table copy as it would do with my database!? On the other hand I read that this would be an implicit conversion and thus it would be a matter of seconds instead of hours.
Instead of setting up a SQL Server and reproducing it locally which could take pretty much time I would like to ask you if the answer is a simple "yes, it does a table copy", or "float8 to bigint, no it's not a problem".
Thx for your help!
It appears to do this one in place.
CREATE TABLE T
(
c float,
d int,
v varchar(10)
)
INSERT INTO T VALUES (1,1,'')
alter table T alter column c bigint
SELECT sc.name, sipc.leaf_offset, sipc.max_inrow_length
FROM sys.partitions sp
JOIN sys.system_internals_partition_columns sipc
ON sp.partition_id = sipc.partition_id
left JOIN sys.columns sc
ON sc.column_id = sipc.partition_column_id AND sc.object_id = sp.object_id
WHERE sp.object_id = OBJECT_ID('dbo.T')
DROP TABLE T
Returns
name leaf_offset max_inrow_length
---- ----------- ----------------
NULL 4 8
d 12 4
v -1 10
c 4 8
The old float
column is still visible but the bigint
column has just taken over the slot. It will need to update every record of course though to make this change.
精彩评论