开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜