The fastest way to change the column data type in SQL Server

Question

In SQL Server 2008, I have a table with about 3 million records. I want to change the type of one of my columns from float to int. But it looks like it will take a long time. Is there any way to do this very fast?

Answer 1

Drop any constraints, indexes, and triggers for this table; they also slow down the update.

Answer 2

Add a new int column to the table, update the new column to the value of the old column, delete the old column, rename the new column.

I have a feeling that it will be faster, but I could be wrong!

, float, .

Answer 3

" " .

, , .

Answer 4

, , SSMS . . , SSMS -, , .

Answer 5

The ALTER table is usually faster than using SSMS. In any case, I agree with doogstar, since you need to convert all the data to int, I would create a new column to populate it (which you can do in batches to improve performance and prevent table locking), then discard the old column and rename new.